First off, do not confuse Date/Time values with DURATION
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.
Formatting does not change ANYTHING!!! The underlying vales remains what it was.
However, you can also insert a Custom format, using
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
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