Date/Time conversion

[Mcgr0199]

Hi, I'm trying to add the date and time from two columns to create another column with a serial time. In other words I'm importing a large dataset and need to create another column that converts General Mountain Date and General Mountain Time (two separate columns) to a Serial Time. Is that possible? Thanks for you help!

 

[Brianwarnock]

I don't understand the phrase Serial Time.

Brian

 

[JoanneJames]

Dont know if this is what he means, but this came up in search

Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a serial date, or serial date-time.

 

[Mcgr0199]

Yes, that is what I mean. The serial date is a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt

We do a lot of work in excel with data and the serial date provides a unique value/time stamp associated with each datum.

I can assign the serial date in excel, but its time consuming/inefficient. If I could find a way to have it set up automatically in Access that would be ideal.

Thanks!

 

[Brianwarnock]

Ok so the term Serial Date is the name used for the standard form that the date/time is stored, what we see is that formatted, to convert that in display just CDBL, so in a query you could

CDBL(nz(datefield,0) + nz(timefield,0))

The NZ is required to avoid errors when there is no value, its up to you whether you include it or not.

Brian

 

[Mcgr0199]

Thanks, that worked great! Sorry for the long response time.