Date Night in Excel

It is September 1^st and for many of us this Date signals the coming of fall.  (It’s 85 and sunny here in Southern California, but my roots are in Minnesota, so I remember…).

Speaking of Dates, it is good to know how Excel treats this information, as it is not entirely intuitive at times.

Dates are treated as Sequential Numbers in Excel.  For instance, September 1, 2011 is represented (behind the scenes) as 40787.  September 2, 2011 is 40788, and so forth.  Therefore, if you subtract Today’s Date from September 23, 2011, (the official first day of fall this year), you get 40809 – 40787 = 22.  22 days until the true first day of autumn!

If you want to use a handy function for Today’s Date, you can use the following to get the above results: 

=September 23, 2011 - TODAY()   (Note: You may have to format the results as “General”)

If you want to get a bit Fancier, (but still reasonably basic), you  can try out a formula that determines if the Date you have chosen is Today, the Future, or the Past, you can use the following:

=IF(Date_Chosen = TODAY(), “Today”, IF(Date_Chosen < TODAY(), “Past”, “Future”)

Dates in Excel; They may not be as Fun as Date Night, but they are interesting and very useful when you know how they work.  We will be further exploring How Excel Handles Dates in the future.  In the meantime, do a Little Exploring, and don’t forget to format your dates as “General” to see what is going on behind the scenes.

Cheers!