Chatting about Excel and More

I could have sworn I had blogged about Date Functions awhile ago but apparently not.
Let me start with some basics.
A lot of people struggle with Date Functions and I think a lot of the problem arises because of formatting problems as as the fact that many of the answers are volatile.

If you don't know about =TODAY() or =NOW(), you should check them out. I use them constantly.
=TODAY() returns today's date. It is a volatile function. By volatile, I mean that it changes.
Coming from Lotus - oh so long ago- I hated these functions. I think I spent 2 hours once trying to figure out what I was doing wrong - only to realize that yes you really do need the empty parentheses to get the function to work!

If I type =TODAY() in an Excel spreadsheet today, which is February 1, it will display February 1. But, if I save the spreadsheet and open it February 2, it will display February 2 without me having to do anything. Open it on March 15th and it will display March 15th.

=NOW() works the same way, only it displays the current date and the current time.

People tend to be a little wary of these because sometimes the display looks hinky..(is that word?)
=TODAY() only displays the date so if you format it as date and time, it always displays 12:00 which of course looks strange.

Sometimes, if you are using these functions in a mathematical calculation, Excel tries to help you out and just confuses you. For example, if I type =TODAY() + A1 and A1 contains the value 10, I expect the answer to be February 11, 2010 since 10 days from today, February 1, would be February 11th. However, sometimes Excel surprises you and gives you an answer like the one below:

Generally the person who typed the formula, assumes they did something wrong. Well, you didn't. Excel sees that you are adding and so instead of displaying it in a date format - it displays it in a number format. All you have to do is reformat it as a number and it will display properly as 2/11/10. The good news is that Excel 2007 seems to have taken care of this problem but sometimes when you expect a number Excel displays it as a date or vice versa. So, before you decide it is user error on your part, re-format the cell.