<$BlogRSDURL$>

June 10, 2004

The DATE function, Part 1

The DATE function is definitely one of my favorites. It works like this, =DATE(Year,Month,Day). So =DATE(2004,6,10) will give me June 10, 2004.

Here's a couple of useful tricks.

First of the Month
=TODAY will give today's date, so =DATE(YEAR(TODAY()),MONTH(TODAY()),1) will give you the first of the current month. Add or subtract to MONTH(TODAY()) for different months, as in =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1) will give you the first day of next month while =DATE(YEAR(TODAY()),MONTH(TODAY())-1,1) will give you the first of last month.

End of the Month
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0) will give you the last day of the current month. Add or subtract months as above to go forward or backward in time as you like. Who said time travel was impossible?

A slightly more complex formula next time...

Comments:
Just a note to thank you for your blogging. Although I haven't posted anything, I read your blog every day. You provide some good hints that sometimes I forget.

Keep it up. It is appreciated by the "silent majority".
 
Thanks for the nice comment!

It's good to know people read my blog and even better to know I'm appreciated!

Some changes are in the works, so hopefully the blog will get better in future.

Until then, please keep visiting. Thanks again.
 
I am creating a spreadsheet that will add days to a date based on quarters. So if I want to add 1 quarter to the listed date, I will add 90 days; 2 quarters 180 days and so forth. After I get the date calculated, I need it to round to the end of the month for the day listed no matter what. So 6/27/2012 will need to reflect 6/30/2012. How do I nest these two different functions?
 
I am creating a spreadsheet that will add days to a date based on quarters. So if I want to add 1 quarter to the listed date, I will add 90 days; 2 quarters 180 days and so forth. After I get the date calculated, I need it to round to the end of the month for the day listed no matter what. So 6/27/2012 will need to reflect 6/30/2012. How do I nest these two different functions?
 
Post a Comment

This page is powered by Blogger. Isn't yours?