Forum Discussion

g1davies's avatar
g1davies
Copper Contributor
Apr 02, 2024

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

Excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Apr 02, 2024

    g1davies 

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

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 02, 2024

    g1davies 

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

     

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Apr 02, 2024

      HansVogelaar 

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

       

    • g1davies's avatar
      g1davies
      Copper Contributor
      Apr 04, 2024
      That worked perfectly, Thank you so much

Resources