Counting back dates to the last day of a month

2
votes

I have the following timeline in Excel:

       A               B              C               D
1    31-10-2017    30-11-2017     31-12-2017      01-05-2018

In Row 1 the dates are calculated back based on the value entered in Cell D1. The other dates are always counted back to the last day of the month. For this I use the following formula in Cells A1:C1

C1=DATE(YEAR(D1),MONTH(D1-1),)

This formula works fine for all dates except for the first day of a month. For example if I enter 01-01-2018 into Cell D1 the value in Cell C1 is 30-11-2017 and not 31-12-2017.

Do you have any idea how I have to modify my formula so it always counts back to the last day of a month no matter if I enter the first day of a month or any other day into Cell D1.

1
excelexcel-formula
EOMONTH is the function you need: exceljet.net/excel-functions/excel-eomonth-function – Lowpar
...or you can use =D1-DAY(D1) – barry houdini

1 Answers

2
votes

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

Day 0 will drop the date value to the day before the first day of the month (your last day of previous month)