INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
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!
- Students Click Here
*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
|
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
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
Also, check out Microsoft
|
Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum
|
|
|
|
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:
Talk To Other Members- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close
