Importing & Scrubbing Date-Time Stamps in Access/Excel

[m2r]

Scenario: I routinely import date/time-stamped records into either Excel and/or Access 2002 from a external databases. Here is an example:

2004-06-30 23:59:00 (This was pulled using MS Query from MS Project stored into Access or Excel)
or
2003-09-18 10:35:14.000 (This was pulled using MS Query from a Sybase into Access or Excel)
Question:
1. I often need to have the Month, Day, Year, Hour, Seconds split out into different columns. How do I do this without using VBA. What is the quickest Method?

2. How do I calculate date differences between records? I am confused because of the usage of the the way Excel treats dates as a sequential number, but displays it as a date.

Any help would be greatly appreciated!!!

Thanks,
M

 

[garyd1234]

Re: Importing & Scrubbing Date-Time Stamps in Access/Exc

Its not bad using VBA

mo= Mon(Date) will get the month off of todays date. Substitute your variable.
da= Day(Date)
yr= Year(Date)

Use DateDiff to get the difference.

 

[bat17]

For Access the real question is why split them? In a report you could just put the field in as many times as needed and use formatting in the control to govern whether you see it formatted as a Day or a Month or whatever.

Finding the difference in dates between records in Access is a bit more complex as you have to have a way of defining the previous record.

Dates are stored as a sequential number of days starting 01/01/1904 (I think J ) Time is a fractional part of the day, the rest is just formatting for how to display it.


HTH

Peter