need to calculate NETWORKDAYS based on a checked checkbox in a different column "Days Away"

OshaK

I have a Date column "Date" that lists all 365 days by month. The second column is a Checkbox type called "Days Away" where admin check which days they are away from the office.

I need to calculate the total of the days away excluding weekends and holidays counting only the days that are checked in the second column. Ideally by individual months.

So i need to check the status and if the checkbox is checked, count that day IF it's not a holiday or a weekend.

i'm trying to use

=IF([Days Away], 1, NETWORKDAYS(Date3: Date33), 0, [holidays])

but it returns UNPARSEABLE.

Thank you!

Olya

Paul Newcome

What are you using to record your holidays? Is that a list on another sheet?

OshaK

thank you, @Paul Newcome sorry, I thought it was a function of Smartsheet. for now, i'm not recording it anywhere, but this
=IF([Days Away], 1, NETWORKDAYS(Date3: Date33), 0)

returns unparseable too. Is there a way to track the holidays? i'm working off the Calendar template in Smartsheet.

Paul Newcome

You can create another sheet that simply lists out all holiday dates down a date type column and then reference that in your formula.

Are you able to provide a screenshot that includes data manually entered to reflect what you are trying to automate?

OshaK

@Paul Newcome thank you. I need to calculate the number of days ONLY if they are checked in Days Away, so basically the number of checkboxes checked in Days Away column, but exclude weekends and holidays from the Date column. Just a total per each month. Thank you.

Paul Newcome

Where exactly is this count going?

OshaK

in a different sheet. I have 64 people x 12 months. TY!

Paul Newcome

For March of 2025, you would use something along the lines of

=COUNTIFS({Source Sheet Date Column}, AND(IFERROR(WEEKDAY(@cell), 0) >= 2, IFERROR(WEEKDAY(@cell), 0) <= 6, IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2025))

In the above, you would change the 3 to whatever month number you need and the 2025 is the year you need to pull in.

OshaK

thank you, @Paul Newcome! would it calculate only the dates that are checked in the Days Away column? and could you show me how to exclude the holidays? TY!

Paul Newcome

=COUNTIFS({Source Sheet Date Column}, AND(IFERROR(WEEKDAY(@cell), 0) >= 2, IFERROR(WEEKDAY(@cell), 0) <= 6, IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2025), {Source Sheet Check Column}, @cell = 1)

The above will incorporate the checkbox. To incorporate the holidays, you would create a separate sheet that has all holiday dates going down a single column. Then the source sheet would would need another checkbox column that would use this column formula:

=IF(COUNTIFS({Holiday Sheet Dates Column}, @cell = Date@row) > 0, 1)

Then you would add to the first COUNTIFS another range/criteria set similar to the last to reference the new checkbox in the source sheet and exclude rows that are checked.

OshaK

@Paul Newcome thank you so much for your help and sorry for a delay.

I was able to make the holiday formula work and the main formula work separately and the first adds the check boxes for holidays and the second calculate checked days away minus weekends.

But i ran into an interesting problem - because i'm using SS calendar template, Jan 1 is needed in the first row to trigger autopopulation of the rest of year dates. so my workday off formula calculates it as one extra day in January. If there a way to exclude the first on the row 1?

so it returns 3 even though there are only 2 holidays in January. How can i fix that?

Thank you so much!