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.
Format Cells: ‘d;d;;@’ to show date and text and hide 0Conditional Formatting:draw calendar bordersgrey out Sat and Sun dateshighlight holidays in redhighlight milestone dates in corresponding colorsCA/US Holidays: drop-down list to switch holidaysHolidays tab: refer to the attached excel file
=LET(
start_date,$B$6,
end_date,$B$14,
mth_num,DATEDIF(start_date,end_date,”m”),
mth_num_mult_3,CEILING.MATH(mth_num,3),
mth_num_div_3,mth_num_mult_3/3,
cal_col_num,7*3,
cal_horiz,DROP(
REDUCE(0,SEQUENCE(mth_num_mult_3,,0),
LAMBDA(a,v,HSTACK(a,
LET(
mth_start,EOMONTH(start_date,v-1)+1,
mth_arr,EOMONTH(start_date,v-1)+1,
cal_head,HSTACK(EXPAND(“”,,3,””),TEXT(mth_start,”mmm-yyyy”),EXPAND(“”,,3,””)),
cal_week,TEXT(SEQUENCE(,7),”ddd”),
cal_body,SEQUENCE(5,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_3,,0),
LAMBDA(a,v,VSTACK(a,
CHOOSECOLS(cal_horiz,SEQUENCE(cal_col_num,,1+cal_col_num*v))))),
1)
)
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. Format Cells: ‘d;d;;@’ to show date and text and hide 0Conditional Formatting:draw calendar bordersgrey out Sat and Sun dateshighlight holidays in redhighlight milestone dates in corresponding colorsCA/US Holidays: drop-down list to switch holidaysHolidays tab: refer to the attached excel file =LET(
start_date,$B$6,
end_date,$B$14,
mth_num,DATEDIF(start_date,end_date,”m”),
mth_num_mult_3,CEILING.MATH(mth_num,3),
mth_num_div_3,mth_num_mult_3/3,
cal_col_num,7*3,
cal_horiz,DROP(
REDUCE(0,SEQUENCE(mth_num_mult_3,,0),
LAMBDA(a,v,HSTACK(a,
LET(
mth_start,EOMONTH(start_date,v-1)+1,
mth_arr,EOMONTH(start_date,v-1)+1,
cal_head,HSTACK(EXPAND(“”,,3,””),TEXT(mth_start,”mmm-yyyy”),EXPAND(“”,,3,””)),
cal_week,TEXT(SEQUENCE(,7),”ddd”),
cal_body,SEQUENCE(5,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_3,,0),
LAMBDA(a,v,VSTACK(a,
CHOOSECOLS(cal_horiz,SEQUENCE(cal_col_num,,1+cal_col_num*v))))),
1)
) Read More
