Forum Discussion
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 the...
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.
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 😞
- Unfortunate, I was hoping to see an alternative which can be used in general instead of REDUCE/STACK method... But nice version of yours, looking at it I updated my formula which has the 3rd input 'mth_in_row_num' to expand/shrink the calendar horizontally.
Please do let us know if you come up with a faster alternative later!Better late than never... I revisited this today with improved methods, which can handle a date range spanning hundreds of years efficiently:
=LET( wrap_count, 3, low, MIN(B7:B15), upp, MAX(B7:B15), num, CEILING.MATH((YEAR(upp) - YEAR(low)) * 12 + MONTH(upp) - MONTH(low) + 1, wrap_count), beg, DATE(YEAR(low), MONTH(low) + SEQUENCE(num,, 0), 1), pad, EXPAND("", ROWS(beg), 3, ""), lbl, EXPAND(TEXT(beg, "mmm-e"),, 4, ""), hdr, IFNA(TEXT(SEQUENCE(, 7), "ddd"), beg), bdy, beg - WEEKDAY(beg) + SEQUENCE(, 42), arr, HSTACK(pad, lbl, hdr, (MONTH(bdy) = MONTH(beg)) * bdy), h, 8, w, 7, n, wrap_count * w, i, SEQUENCE(COUNTA(arr) / n,, 0), j, SEQUENCE(, n, 0), INDEX(arr, 1 + QUOTIENT(j, w) + wrap_count * QUOTIENT(i, h), 1 + MOD(j, w) + w * MOD(i, h)) )Adjust the wrap_count as desired. Cheers!