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.