# Thread: Calculating last day of previous month

1. Loungers - I'm trying to work out a formula that will calculate the last day of a previous month. ie in cell B1 if have the last day of a month - say 28/2/09 in A1 I need the last day of January - 31/1/09, or 30/6/09 in A1 - B1 needs to be 31/5/09

I have tried subtracting one day and one month from the date in A1, however that gives dates that don't exist or not the last day of the previous month.

I hope this makes sense - any thoughts?

2. [quote name='verada' post='782811' date='03-Jul-2009 12:28']Loungers - I'm trying to work out a formula that will calculate the last day of a previous month. ie in cell B1 if have the last day of a month - say 28/2/09 in A1 I need the last day of January - 31/1/09, or 30/6/09 in A1 - B1 needs to be 31/5/09

I have tried subtracting one day and one month from the date in A1, however that gives dates that don't exist or not the last day of the previous month.

I hope this makes sense - any thoughts?[/quote]

The last day of the previous month is one day before the first day of the month you have so:

=DATE(YEAR(B1),MONTH(B1),1)-1

3. Cheers - just what I needed

Thanks

4. [quote name='johnhutchison' post='782818' date='02-Jul-2009 21:04']The last day of the previous month is one day before the first day of the month you have so:

=DATE(YEAR(B1),MONTH(B1),1)-1[/quote]
Hi John
Your formula can be simplified slightly to:
=DATE(YEAR(B1),MONTH(B1),0)

5. [quote name='wdwells' post='782824' date='03-Jul-2009 14:53']Hi John
Your formula can be simplified slightly to:
=DATE(YEAR(B1),MONTH(B1),0)[/quote]

Thanks Don

In theory, I "know" that, but in practice I find the "day before the first day of the month" concept easier to remember when I need to do this.

6. Yet another option: if the date is in A1, the formula

=A1-DAY(A1)

will return the last day of the previous month.

7. [quote name='HansV' post='782844' date='03-Jul-2009 05:54']Yet another option: if the date is in A1, the formula

=A1-DAY(A1)

will return the last day of the previous month.[/quote]

Thanks Hans

I've seen you use that before but had not remembered it. I like it for its brevity.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•