Tag Archives: TODAY()

Excel Date System Basics

Excel handles Date values as integer numbers. Number one means 1st of January 1900, two means 2nd of January 1900 and so on. Today (27th of February 2013) is day number 41332. So date number can be understood as the number of days since 31st of December 1899.

The first day that Excel supports is 1st of January 1900 and if you need earlier dates, Excel’s build in date functions are not working.

Excel actually supports two different Date Systems: In addition two this 1900 Date System you can also use 1904 Date System where number one means 1st of January 1904. This can be selected on Calculation settings but I recommend to use 1900 Date System to maintain better compatibility to e.g. other spreadsheet programs.

Excel handles times as decimal numbers between 0 and 1 and so 41332,41667 means that the date is 27th of February 2013 and time of the day is 10AM.

Following video demonstrates this.

 

Difference between TODAY() and NOW() functions

Excel has two similar time functions TODAY() and NOW(). These functions are not the same. Difference is that TODAY() function returns only current date (time value is 12AM or 0:00) and NOW()-function returns current date and current time.

Following video demonstrates this:

 

How to use Excel Conditional Formatting to highlight past dates

Excel has very powerful feature called conditional formatting. In this post I will demonstrate how to use the feature to automatically highlight past dates in different way than future dates.

  • I use TODAY()-function to get current date
  • I set conditional formatting so that when the value of the cell is less than TODAY(), it will be formatted

Following video demonstrates this: