SOLVED

# Start and End Date of each Month in a date period

Copper Contributor

# 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 than a month it fails.

Thanks

Gavin

6 Replies
best response confirmed by g1davies (Copper Contributor)
Solution

# Re: Start and End Date of each Month in a date period

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))

# Re: Start and End Date of each Month in a date period

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)
)``````

# Re: Start and End Date of each Month in a date period

Congratulations on getting the format exactly the way the OP wanted!

# Re: Start and End Date of each Month in a date period

That worked perfectly, Thank you so much

# Re: Start and End Date of each Month in a date period

That also worked perfectly, thankyou
1 best response

Accepted Solutions
best response confirmed by g1davies (Copper Contributor)
Solution

# Re: Start and End Date of each Month in a date period

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))