Results 1 to 6 of 6
  1. #1
    mlrucci is offline Competent Performer
    Windows 7 64bit Office 365
    Join Date
    Apr 2018
    Posts
    202

    Conversion of date time to serial number


    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.

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try CDbl():

    ?CDbl(now())
    44690.4721759259
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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.

  5. #5
    mlrucci is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2018
    Posts
    202
    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!


  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Conversion of Date/Time field from Access to SQL
    By Robert2150 in forum SQL Server
    Replies: 4
    Last Post: 10-02-2016, 01:37 PM
  2. Replies: 1
    Last Post: 10-25-2013, 03:38 PM
  3. Replies: 1
    Last Post: 09-06-2011, 05:24 AM
  4. Replies: 6
    Last Post: 01-04-2011, 05:43 PM
  5. Replies: 5
    Last Post: 02-08-2006, 08:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums