Microsoft Office Tutorials and References

In Depth Information

**Dates and Times in Excel**

cell. Every day is equivalent to the number 1, and every week is equivalent

to the number 7. This is very simple to understand.

When you see 42185 instead of June 30, 2015, Excel calls the 42185 a serial

number. Some of the Excel functions discussed here convert from a serial

number to text that looks like a date, or vice versa.

For time, Excel adds a decimal to the serial number. There are 24 hours in a

day. The serial number for 6:00 a.m. is 0.25. The serial number for noon is 0.5.

The serial number for 6:00 p.m. is 0.75. The serial number for 3:00 p.m. on June

30, 2015 is 42185.625. To see how this works, try this out:

11. Open a blank Excel workbook.

22. In any cell, enter a number in the range of 35,000 to 45,000.

33. Add a decimal point and any random digits after the decimal.

44. Select that cell.

55. From the Home tab, select the dialog launcher in the lower-right

corner of the Number group.

66. In the Date category, scroll down and select the format 3/14/01 1:30

PM. Excel displays your random number as a date and time. If the

decimal portion of your number is greater than 0.5, the result will be

in the p.m. portion of the day.

77. Go to another cell and enter the date you were born, using a four-digit

year. (This doesn
’
t work if you are older than 113.)

88. Again select the cell and format it as a number. Excel converts it to

show how many days after the start of the last century you were

born. This is great trivia but not necessarily useful.