SOLVED

Start and End Date of each Month in a date period

Copper Contributor

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.

 

g1davies_0-1712072637071.png

 

Thanks

 

Gavin

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

@g1davies 

Would this be acceptable?

HansVogelaar_0-1712085885002.png

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

 

@Hans Vogelaar 

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

 

@Peter Bartholomew 

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

That worked perfectly, Thank you so much
That also worked perfectly, thankyou
1 best response

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

@g1davies 

Would this be acceptable?

HansVogelaar_0-1712085885002.png

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

 

View solution in original post