Conversion of date time to serial number

[mlrucci]

Good afternoon, I am attempting to convert mm/did/yyy hh:ss AA/PM to a serial number format with the minutes and seconds. I have used CDate and DateValue and continue to get a whole number. not sure what I am missing. Any assistance would be helpful.

[pbaldy]

Try CDbl():

?CDbl(now())
44690.4721759259

[Micron]

cdbl will work on Now(), but not when the date is a string with AM/PM in it. In that case, I'd suggest

cdbl(cdate("5/09/22 3:15:08 PM")) = 44690.6355092593

format with the minutes and seconds

However I wonder if that is what you want because the fractional number represents a decimal value for the portion of a day that has elapsed and not the hours/minutes at that point.

[CJ_London]

I am attempting to convert mm/did/yyy hh:ss AA/PM to a serial number format with the minutes and seconds.

can you clarify what that actually means. Your structure does not include minutes and AA/PM is not valid from a date perspective. Or is that just poor typing?

Is the value an actual string or is it actually a date/time field formatted to the above.

And what do you want to do with the date part?

Or is what you want simply the number of hours x 60 + seconds

Suggest provide some actual examples and what you want as a result.

[mlrucci]

My original format is Date in m/d/yyyy h:nn:ss AM/PM. That was a typo. Sorry about that. I used the cdbl(cdate("5/09/22 3:15:08 PM")) = 44690.6355092593 and it worked great. I am using this to create a 1:1 relationship for specific documents. I am using this in conjunction with the specific document name. I really appreciate the assistance with this. You guys rock it!

[CJ_London]

My original format is Date in m/d/yyyy h:nn:ss AM/PM

so if it is a date field you just need cDbl(myDate) - you might as well just use the date because 44690.6355092593 is how the data is actually stored

?cdate(44690.6355092593)
09/05/2022 15:15:08

just be aware that doubles do not make good fields for PK/FK as they do not have the precision required.