Forum Discussion

g1davies's avatar
g1davies
Copper Contributor
Apr 02, 2024
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...
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))

 

  • g1davies's avatar
    g1davies
    Copper Contributor
    Apr 04, 2024
    That worked perfectly, Thank you so much
  • 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 also worked perfectly, thankyou
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Apr 03, 2024

        PeterBartholomew1 

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

Resources