Forum Discussion
Solved
Start and End Date of each Month in a date period
Hi, I hope you can help, I am trying to see the start and end dates per month within a date period. Like the below, I can get it to work but it is not dynamic so when I change the dates by more t...
Would this be acceptable?
The formula in D1 is
=LET(startdate, B2, enddate, B3, firstofmonth, startdate-DAY(startdate)+1, monthstart, EDATE(firstofmonth, SEQUENCE(, DATEDIF(firstofmonth, enddate, "M")+1, 0)), startdates, IF(monthstart<startdate, startdate, monthstart), monthend, EOMONTH(startdates, 0), enddates, IF(monthend>enddate, enddate, monthend), VSTACK(TEXT(startdates, "mmmm"), startdates, enddates))
I like your format and the way you included the dynamic month header.
My attempt was
= LET(
months, 1 + DATEDIF(startDate, endDate, "M"),
monthEnd, EOMONTH(startDate, SEQUENCE(months, , 0)),
periodEnd, IF(monthEnd>endDate, endDate, monthEnd),
periodStart, DROP(VSTACK(startDate, monthEnd + 1), -1),
TOROW(HSTACK(periodStart, periodEnd))
)but with a separate formula for the heading
= LET(
months, 1 + DATEDIF(startDate, endDate, "M"),
monthEnd, EOMONTH(startDate, SEQUENCE(months, , 0)),
monthName, EXPAND(TEXT(monthEnd,"mmmm"),,2,""),
TOROW(monthName)
)
Congratulations on getting the format exactly the way the OP wanted!
