CODEWITHSUNDEEP
excelexcel-formula
Tom Lau
Tom Lau

Reputation: 109

excel dynamically generate weekdays unitl today (included)

My objective is to have 1 formula (better) or macro that can automatically generate all weekdays from a given day cell reference until today (included).

Say I have a A1 cell with "2022-01-01", how can I generate a column with values that will include all weekdays until today? (this will be a dynamic length as everyday is updated)

Like:

enter image description here

Edit: using Office 2019 so SEQUENCE() function not working

Upvotes: 0

Views: 195

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

put this in your first cell:

=WORKDAY(A1-1,SEQUENCE(NETWORKDAYS(A1,TODAY())))

This requires the dynamic array formula Sequence. It will spill the results down.

enter image description here

With out SEQUENCE, put this in the first cell and copy down till you get blanks and beyond if you want each new day to continue to appear:

=IF(WORKDAY(A1-1,ROW($ZZ1))>TODAY(),"",WORKDAY(A1-1,ROW($ZZ1)))

Upvotes: 3

Related Questions