Good afternoon,
What's the best way to accomplish this task here... I'm a bit stumped. I feel like I have all the parts but my brain just isn't combining them.
I'm making an attendance roster.
I need a number of columns equal to the number of working days in a month. Each month will have it's own sheet in the workbook. At the start of each sheet, I have the first day of the month, ex. March, 1, 2022, but it's formatted so that it simply appears only as "MARCH 2022" in cell I2.
Starting at E3, I have the formula
This works great and all, but it's obviously pushing March 1 + 31 days after it that are weekdays. On a different sheet, I have the NETWORKDAYS listed for each month in 2022. This also excludes a range of dates named holidays.
So, for example, March has 23 working days in it according to the NETWORKDAYS minus any holidays. I want to input this "23" into the formula WORKDAY(SEQUENCE) formula back on the "March" sheet.
For end game here, I want to just copy each sheet and simply change the date in cell I2 to a different month's date and have the WORKDAY(SEQUENCE) automatically update the number of columns it produces based on the number of NETWORKDAYS for that month. ie. March 2022 will have 23 columns, April 2022 will have 20 columns, May 2022 will have 21 columns, etc. Is there a way to change the date in I2 of any sheet of a month and have the WORKDAY(SEQUENCE) formula update with the correct number of working days?
What's the best way to accomplish this task here... I'm a bit stumped. I feel like I have all the parts but my brain just isn't combining them.
I'm making an attendance roster.
I need a number of columns equal to the number of working days in a month. Each month will have it's own sheet in the workbook. At the start of each sheet, I have the first day of the month, ex. March, 1, 2022, but it's formatted so that it simply appears only as "MARCH 2022" in cell I2.
Starting at E3, I have the formula
Excel Formula:
=WORKDAY.INTL(I2-1,SEQUENCE(1,31))
This works great and all, but it's obviously pushing March 1 + 31 days after it that are weekdays. On a different sheet, I have the NETWORKDAYS listed for each month in 2022. This also excludes a range of dates named holidays.
Excel Formula:
=NETWORKDAYS(D17,EOMONTH(D17,0),holidays)
So, for example, March has 23 working days in it according to the NETWORKDAYS minus any holidays. I want to input this "23" into the formula WORKDAY(SEQUENCE) formula back on the "March" sheet.
For end game here, I want to just copy each sheet and simply change the date in cell I2 to a different month's date and have the WORKDAY(SEQUENCE) automatically update the number of columns it produces based on the number of NETWORKDAYS for that month. ie. March 2022 will have 23 columns, April 2022 will have 20 columns, May 2022 will have 21 columns, etc. Is there a way to change the date in I2 of any sheet of a month and have the WORKDAY(SEQUENCE) formula update with the correct number of working days?