INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...(I) have been able to get my problems solved from
past messages and also new posts that other users have responded to promptly..."
Geography
Where in the world do Tek-Tips members come from?
|
Date/Time Functions
|
Understanding Dates and Times & why they seem to be so much trouble?
Posted: 20 Apr 05 (Edited 25 Oct 11)
|
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!!!
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
