Time format

[Tascja]

Hello,
I have a table where I enter the time of an appointment in my TIME field. Its Data Type is Date/Time and its format is Medium Time. In Datasheet View the time display as hh:ss AM/PM (ie. 12:00 PM) but when I export the table to excel the time is displayed as a General Date (ie. 00/01/1900 12:00:00 PM). What am I doing wrong that the time exports as a Date + Time and not as it's displayed in Database?
Any help or suggestions would be greatly appreciated.
Thanks,


Natascja

[Micron]

Based on your description, I'd say you might not fully understand how date/time is stored and how the data interacts with different programs. It is usually best to store date AND time in one field and let your form controls display what parts of it you want to see. Excel shows 00/01/1900 12:00:00 PM because your source data contains no date (other than the default, but keep reading), so Excel defaults to the starting point for system dates, which is January 01 1900. Date/time is a floating point double precision number that can be formatted to appear in numerous ways. What you're seeing in Access is the format of the very same date that Excel is seeing. You can work around this with various means, subject to your level of expertise. The easiest might be to forego Excel in the first place if you don't really need it. Unless you're graphing or doing complex calculations, I have to wonder why use Excel at all when you have db data. Another possibility is to have macros in your sheet that fix the column format after the import/export, or simply do this manually.

In short, the appearance in Excel isn't a mistake you're making - beyond possibly what I've already said about not splitting times from dates

[isladogs]

Just to add to Micron's excellent explanation.
Access uses the same idea as Excel but has a different starting point for dates.
In Access, day zero was 30 Dec 1899.
At the time it was decided, developers in the two apps clearly weren't communicating And afterwards it was too late to change things.
However as date calculations in the two apps are internally consistent, the discrepancy doesn't actually matter

[Tascja]

Thanks for your explanation Micron! I need to keep using Excel exports for some of my other tasks outside of Access so I'll take your suggestion to format the column to only display time.