Tuesday, October 12, 2004

Formatting Dates and Times

There are some useful custom formats you can use when working with dates and times.

Dates
Select a cell with a date in it and then from the menu click Format - Cells and choose the Number Tab.
Select the Custom option.
The Type box on the right hand side is editable. Try these custom combinations.
d = Date in single digits Eg 8
dd = Date with two digits (leading zero for dates less than 10) Eg 08
ddd = Day of the week - short form 3 letter abbreviation Eg Fri
dddd = Day of the week - whole word Eg Friday
m = Month in single digits Eg 6
mm = Month with two digits (leading zero for dates less than 10) Eg 06
mmm = Month - short form 3 letter abbreviation Eg Jun
mmmm = Month - whole word Eg June
yy = Year in last two digits only Eg 04
yyyy = Year in full Eg 2004

Excel will allow you to use various separators including space, dashes, slashes etc. Experiment with what you want to see.

Time
Time is interesting. Negative time can be calculated but not displayed.
However there are some tricks to handling addition of time. It is all about understanding the bases. Time is managed in 4 parts. Seconds (base 60) Minutes (base 60) Hours (base 24) and AM/PM.
Type a time into a cell. The syntax to use is hh:mm:ss. You can shorten this by just typing the hour followed by the : to get the hour Eg 10: will give you 10:00:00 AM

However adding 10:00:00 to 15:00:00 will give you 1:00:00, because 10 hours plus 15 hours equals 25 hours which is 1AM. If you want to see 25:00 (for example in a timesheet spreadsheet) then format the cell custom to [h]:mm:ss
The bracketed h tells Excel to display the actual value and not parse it in base 24 format.


Posted by: Jethro | 11:44 PM |