Forum Discussion

DaeyunPablo's avatar
DaeyunPablo
Brass Contributor
Aug 08, 2024

Dynamic Calendar in Excel 365

Hi,

 

I create a dynamic array formula for projects at work. It takes project start & end dates and returns a calendar where its duration is based off of those two inputs. Please let me know if there's any errors I missed or it can be improved in any ways.

 

  • 3 inputs to define the calendar size: start_date & end_date (vertical) and mth_in_row_num (horizontal)
  • Format Cells: 'd;d;;@' to show date and text and hide 0
  • Conditional Formatting:
    • draw calendar borders
    • grey out Sat and Sun dates
    • highlight holidays in red
    • highlight milestone dates in corresponding colors
  • CA/US Holidays: drop-down list to switch holiday region
  • Holidays tab: refer to the attached excel file

Edit: The calendar formula and conditional formatting have been updated as djclements pointed out the rows of calendar should be 6, not 5, to capture the missing 30th or 31st in some months.

 

 

=LET(
    start_date,$B$7,
    end_date,$B$15,
    mth_in_row_num,3,

    mth_num,(YEAR(end_date)-YEAR(start_date))*12+(MONTH(end_date)-MONTH(start_date))+1,
    mth_num_mult,CEILING.MATH(mth_num,mth_in_row_num),
    mth_num_div,mth_num_mult/mth_in_row_num,
    cal_col_num,7*mth_in_row_num,
    cal_horiz,DROP(
        REDUCE(0,SEQUENCE(mth_num_mult,,0),
            LAMBDA(a,v,HSTACK(a,
                LET(
                    mth_start,EOMONTH(start_date,v-1)+1,
                    cal_head,HSTACK(INDEX("",SEQUENCE(,3)^0),TEXT(mth_start,"mmm-yyyy"),INDEX("",SEQUENCE(,3)^0)),
                    cal_week,TEXT(SEQUENCE(,7),"ddd"),
                    cal_body,SEQUENCE(6,7,mth_start-WEEKDAY(mth_start)+1),
                    cal_body_filt,(MONTH(cal_body)=MONTH(mth_start))*cal_body,
                    VSTACK(cal_head,cal_week,cal_body_filt))))),
        ,1),

    DROP(
        REDUCE(0,SEQUENCE(mth_num_div,,0),
            LAMBDA(a,v,VSTACK(a,
                CHOOSECOLS(cal_horiz,SEQUENCE(cal_col_num,,1+cal_col_num*v))))),
        1)
)

 

excel
Formulas and Functions
office 365
  • djclements's avatar
    djclements
    Bronze Contributor
    Aug 09, 2024

    DaeyunPablo Looks pretty good. Just one comment for now... monthly calendars should contain 6 rows of data (not 5). Months beginning on a Friday or Saturday take up 6 weeks on the grid. There are a number of months on your sample calendar that are missing the last day or two (Mar-2024, Jun-2024, Mar-2025, Aug-2025, etc.). 😉

     

    I'll work on an array manipulation alternative to the REDUCE / STACK method when I have some spare time. Cheers!

    • DaeyunPablo's avatar
      DaeyunPablo
      Brass Contributor
      Aug 09, 2024
      Thanks a lot for catching the error! I updated the attached file. I'll be waiting for an alternative to REDUCE/STACK method as well. There's a project I'm working on and and that method gets quite heavy if there are lots of inputs and the formula become complex. It's still great, but I'd like to try a new one if it's more efficient.
      • djclements's avatar
        djclements
        Bronze Contributor
        Aug 09, 2024

        DaeyunPablo Here's my array manipulation variant, using TOCOL, IF, SORTBY and WRAPROWS:

         

        =LET(
    wrap_count, 3,
    start_date, MIN(B7:B15),
    end_date, MAX(B7:B15),
    months, CEILING.MATH((YEAR(end_date) - YEAR(start_date)) * 12 + MONTH(end_date) - MONTH(start_date) + 1, wrap_count),
    month_start, DATE(YEAR(start_date), MONTH(start_date) + SEQUENCE(months,, 0), 1),
    period_start, month_start - WEEKDAY(month_start) - 13,
    period_end, period_start + 55,
    seq_start, MIN(period_start),
    all_dates, SEQUENCE(, MAX(period_end) - seq_start + 1, seq_start),
    test, (period_start > all_dates) + (period_end < all_dates),
    period_dates, TOCOL(IF(test, NA(), all_dates), 2),
    first_dates, TOCOL(IF(test, NA(), month_start), 2),
    wrap_ID, INT(SEQUENCE(ROWS(period_dates),, 0) / (wrap_count * 56)),
    week_ID, TOCOL(IF(SEQUENCE(months), TOROW(IF(SEQUENCE(, 7), SEQUENCE(8))))),
    sort_ID, wrap_ID * 8 + week_ID,
    output, IF(week_ID = 1, IF(WEEKDAY(period_dates) = 4, TEXT(first_dates, "mmm-yyyy"), ""),
        IF(week_ID = 2, TEXT(period_dates, "ddd"), IF(MONTH(period_dates) = MONTH(first_dates), period_dates, 0))),
    WRAPROWS(SORTBY(output, sort_ID), wrap_count * 7)
)

         

        Change the wrap_count if you want to output 2 or 4 months across, for example. I expanded the conditional formatting rules to accommodate up to 4 months across for demonstration purposes, but you could expand them further, if desired.

         

        EDIT: after testing it with a larger date range (with the conditional formatting rules removed), it didn't hold up as well as I'd hoped. The REDUCE / STACK method was actually faster... so sad 😞

Resources