Formatting Date values

[Enchantix]

Hello,

i import tables from excel on a monthly basis to an access 2007 table and the dates that are imported are in a Date/Time format (DD/MM/YYYY HH:MM:SS AM/PM).


im trying to change the date formate to short date only but when i change the format in table properties to short date. When i view table it shows with the time aswell for some of the date values.


ive tried putting DD/MM/YYYY in format but it still shows the time.


even when i ran a qry for the table the date field also showed the time at the end.


any help to fix this would be greatly appreciated.


thanks

 

[BQardi]

Formatting the date won't change the actual date value, formatting is purely for visual purposes in Excel.
To give you a better understanding of how Excel handles dates, you need to know that Excel counts the days from 01.01.1900 up until the date in question. So if you format a cell only containing a date as "General", you would get an integer like 42723. Knowing this, it is only logical that hours, minutes, seconds, etc. would be calculated as decimal numbers. 06.00 would be 0.25 (42723.25), 12.00 = 0.50, 18.00 = 0.75, so on...
To change the value you could simply use a formula to "extract" the date/remove the decimals:
=INT(A1)
(to say it more correctly, the formula converts a number to an integer)

 

[Enchantix]

Thank You for clarifying how excel proccesses dates and how to go about formatting and achieving the desired result via the integer conversion.

 

[seekerarcane]

You can also change the format of date using the following formula;

=TEXT(given date or select cell containing date,"DD:MM:YYYY")

if cell A1= 12/21/2016 10:30

Then,

=TEXT(A1,"DD:MM:YYYY")
= 21:12:2016

 

[BQardi]

You're welcome