Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Office FAQ

Date/Time Functions

Understanding Dates and Times & why they seem to be so much trouble? by SkipVought
Posted: 20 Apr 05 (Edited 2 Jul 15)

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

yy or yyyy 4 character

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

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

Also, check out Microsoft

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close