• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nesting Dates and Rounding Simultaneously

C

cdavidson

New Member
I am so stumped at this process of nesting formulas. I have a spreadsheet that contains a date which is calculated from a previous cell date plus 90, 180, or 270 days respectively as these are reflective of quarters. My issue is how do I get the date to automatically round to the end of the month either the 30th or the 31st depending on the month.


For example. cell C2 contains the quarter end date of 9/30/2012. Cell H2 contains the formula =+C2+180 so the date reflected is 3/29/2013. However, I need the date to be rounded to 3/31/2013 because all data is driven by the date at the end of the month. Therefore, even if the date was 3/3/2013 it would still need to be rounded to 3/31/2012.


Am I making any sense here? Can anybody help me please.
 
Try this!!


=EOMONTH()


put your formula inside the EOMONTH funcion (end of month)


ex:=EOMONTH(C2+180)
 
Let's make things really simple with just 1 function. =)


Behold, the EOMONTH function. Arguments are start date and number of months to step. Returns last day of month. Thus, the three formulas to get your quarters are:

=EOMONTH(C2,3)

which replaces your +90 formula


=EOMONTH(C2,6)

which replaces your +180 formula


=EOMONTH(C2,9)

which replaces your +270 formula


If you're using XL 2003 or older, you'll need to make sure the Analysis ToolPak add-in is enabled.


PS. If you ever just need last day of month for a certain date, you can do:

=EOMONTH(StartDate,0)
 
Bah, Jason beat me by 9 seconds! =P

Actually, I don't think jason's formula will work, as EOMONTH needs to have 2 arguments. =/
 
You must log in or register to reply here.
Back
Top