Friday, October 08, 2004

Birthdays in Excel - TODAY and NOW

Did you know that all dates in Excel are handled as a serial number (starting 1 January 1900)?

Type in =TODAY() in a cell and then format the cell to General or Comma format.
When I did this (8th October 2004) I got 38268.

Now type your birthdate into another cell (using dd/mm/yyyy or mm/dd/yyyy format).
Once again you can format this with General or Comma to see the serial number associated with your birthdate.
You can now subtract your birthdate from today's date and see how many days you have been alive for - Scary huh!

The TODAY() function updates automatically whenever the spreadsheet is recalculated.

Now type =NOW() in another cell. This will give you the serial number associated with the hour, minute and second of the time right now. You may need to increase the number of decimal places showing to get this. This is represented by the part of a whole day (1) that is the correct time. For example midday equals 12:00:00 which translates to 0.5 or half a day.
Note that when you format the cell with =NOW() in it to General or Comma that it shows the serial number for today as the whole number to the left of the decimal point and the part of the day to this second as decimal places to the right.
Eg 38268.9965 is 11:55 PM on the 8th October. Happy Birthday to me!

The TODAY() function updates automatically whenever the spreadsheet is recalculated.


Posted by: Jethro | 11:55 PM |