Excel - VI

Times in Excel

If you are keeping a regular track of it, let's now look at Times in Excel, as promised yesterday! While dates in Excel are seen as whole numbers (serial numbers) Starting from 1, Times are seen as a portion of a day (decimal fractions) with 1 being equal to 24:00:00 or a whole day. This means that:
24:00:00 is equal to 1
18:00:00 is equal to 0. 75
12:00:00 is equal to 0. 5
6:00:00 is equal to 0. 25
We can see this by entering any one of the above times in Excel and then formatting the cell containing the time as General, just as we did with the date. There are only three ways you can enter a valid time in Excel.
Make sure your cells are formatted as General
To enter a time based on a 12-hour clock, type the time followed by a space and then AM or PM. eg: 5:30 PM
Type the time followed by a space and then A or P. eg 5:30 P (Excel will convert this to 5:30 PM)
To enter a time based on a 24-hour clock, type 17:30
If you just typed 5:30 Excel will (by default as your cells are formatted as General by default) base your time on the 24-hour clock and store your time as 5:30:00 AM. In other words by default it sees all unspecified times as 24-hours but stores them based on a 12-hour clock.
The easiest way to see this is to type 5:30 in any cell, then select that cell and look in the Formula bar. No matter which method we use to enter times we must separate the hours, minutes and seconds by a : (colon). If we omit the minutes and/or seconds Excel will (by default) assign zero minutes and/or zero seconds. So entering a time, as 5 P will force Excel to see it as 5:00:00 PM. Obviously this is not the case for a time entered based on a 24-hour. Entering 17 will be seen as nothing more than the number 17.

Entering a Date and Time in Excel

Now that we have covered the fundamentals of dates and times, we can have a quick look at entering dates and times into the same cell. To enter a valid date and time in the same cell, you simply type any valid date, then a space and then any valid time. Excel will then store this as a whole number for the date (serial number) and a portion of a day for the time. (decimal fractions).


Try this:
Type the date and time 12/12/2004 18:00 in any cell
Right click in the cell and select Format cells and click the Number tab, then General under Categories:
Look in the Sample: box and you should see 38333. 75, where 38333 represent the date (serial number) and . 75 represents the time (decimal fraction).