There are quite a few date functions within Microsoft Excel. The one I am looking at today is Date. This function was not my favourite when I was starting out in Excel. In fact I remember dismissing it out of hand. Another trainer, think it was Dino Collins, said to give it another chance that it could be really helpful. Thanks to Dino, I did and yes I do find it useful.
Why? The function itself is quite spread out:
Date ( year , month, day )
I think the thing that turned me off was the fact that I could not just input the current date or a cell reference with a date in and it would magically do it’s thing. No, you have to extract each part of the date. So if the date you were using was 1 January 2010 then the function would look like this:
Date ( 2010 , 1, 1 )
Not great if 1 January 2010 is in cell C4 and you have a list of over 100 to work with. This is where nesting functions within functions is useful, and 3 small functions are going to help.
Year ( date )
Month ( date )
Day ( date )
Instead of typing in an actual date you can use a cell reference, our C4 we were talking about earlier. Year (C4) would return 2010, so by imbedding (or nesting) that function within the Date function we get:
Date ( Year(C4) , Month(C4) , Day(C4) )
All this will return is the date in C4, so why use Date? For calculating. I can add 1 month to get a new date,because if I were to add 30 or even 31 days to the date, that would not always give me the same date next month.
Date ( Year(C4) , Month(C4) + 1 , Day(C4) )
This will give me the same date one month in the future. I have also used this within conditional formating to format cells at a particular time, not just using the above calculation but comparing it to Today()
Date ( Year(C4) , Month(C4) + 1 , Day(C4) ) > Today()
or vise versa depending on what I want formatted. This funtion can be very powerful, even if (like me) you took one look and thought it was not.