Understanding Dates and Times & why they seem to be so much trouble?

[red]
First off, do not confuse Date/Time values with DURATION[/red]

Date/Time values are POINTS IN TIME. There is a particular point in time, that is defined by January 1 2009 or December 25 2009 12:00 PM.

A DURATION is a measure of time between two points. Duration may be expressed in terms of years, months, days, hours, minutes or seconds, but they are not date/time values.

=======================================================
In Microsoft Applications, Date/Time values are just NUMBERS.

Dates are INTEGRAL values and Times are FRACTIONAL values.

If you enter 1 in a cell and FORMAT the cell as a Date, the Displayed Date is 1/1/1900.

Time values are in fractions of a day.

If you enter .75 in a cell and FORMAT the cell as a Time, the Displayed Time is 6:00 PM.


For either Date, Time or both, you can select a Format using Format/Cells/Number Tab and select an appropriate format.

[red]Formatting does not change ANYTHING!!![/red] The underlying vales remains what it was.

However, you can also insert a Custom format, using
[tt]
year
yy or yyyy 4 character

month
m or mm for 1 or 2 character month number
mmm for month abbreviation
mmmm for full month text

day
d or dd for 1 or 2 character day number
ddd for day abbreviation
dddd for full day text
[/tt]
So what happens when you enter a Date or Time into Excel?

1) Excel looks at the string that you entered and guesses that it's a Date or Time. This can be a blessing or it can be frustrating if you are really NOT entering a Date or Time.

2) Using your Regional Settings for Date & Time, Excel parses the string into year, month, day, hour, minute, second. Excel makes other assumptions if you do not enter a complete Date/Time string such as 3/2. For instance, in the USA, Excel assumes that 3/2 is month 3, day 2, current year and 17: is assumed to be 5:00:00 PM.

3) Using the parsed values, Excel CONVERTS the values to a Date/Time Serial Value. For instance, if I entered 2/3 18:, Excel converts this to 38386.75 which can be formatted 2/3/2005 18:00

Also, check out [link http://support.microsoft.com/kb/214094]Microsoft[/link]