Aug 08 2024 03:08 PM - edited Aug 11 2024 04:19 PM
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.
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)
)
Aug 09 2024 05:59 AM
@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!
Aug 09 2024 08:07 AM
Aug 09 2024 09:56 AM - edited Aug 09 2024 06:30 PM
@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 😞
Aug 11 2024 04:25 PM