how to get last day of previous 2 months...

This formula calculates the last day of the previous month…

=DATE(YEAR(TODAY()),MONTH(TODAY()),0)

What would I need to do to adjust so it can give me the last day of the previous 2 months… in this case April. Don’t know if I can take it further but is it possible to just have it give me the 1st day and last day for two months back??? In this case april 1st – april 30th… if that last part can’t be done that’s okay I would like at least to calculate the last day of the previous 2months if possible.

What I’m doing with the current formula is running a report that picks up accounts that are past due before the last day of the previous month… I want to separate them by months… so that’s why I’m asking for this. Thanks.

Originally Posted by jgomez

This formula calculates the last day of the previous month…

=DATE(YEAR(TODAY()),MONTH(TODAY()),0)

What would I need to do to adjust so it can give me the last day of the previous 2 months… in this case April. Don’t know if I can take it further but is it possible to just have it give me the 1st day and last day for two months back??? In this case april 1st – april 30th… if that last part can’t be done that’s okay I would like at least to calculate the last day of the previous 2months if possible.

What I’m doing with the current formula is running a report that picks up accounts that are past due before the last day of the previous month… I want to separate them by months… so that’s why I’m asking for this. Thanks.

Something like:

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,0)

To include the 1st business that you wanted:
="1st - "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,0),"mmmm dd, yyyy")

EDIT: And this one is long but will return 1st - April 30th 2011 (with the words "st" and "th"):

Please Login or Register  to view this content.

Hello Jgomez, try,

For 1st day.

=EOMONTH(TODAY(),-3)+1

For last day,

=EOMONTH(TODAY(),-2)

If you are on XL2003 or prior, this would require AnalysisToolpak AddIns to activate. If you are getting any #NAME error , see the excel help to how to activate it.

This will work with any version.

1st day

=DATE(YEAR(TODAY()),MONTH(TODAY())-2,1)

Last day,

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,0)