NETWORKDAYS definable at row level??

prjessy

I'm using the useful template located at: https://www.smartsheet.com/marketplace/us/templates/team-vacation-planner

The only issue I have is that NETWORKDAYS are defined at a global level it seems in Smartsheet settings, which by default are excluding Saturdays and Sundays, which is perfect for my full time staff.

However, how can I adjust NETWORKDAYS as a formula at row level so that part time staff working different days can still get their NETWORKDAYS recorded accurrately between two date entries. For example, we have a staff member who works Tuesdays, Wednesdays and Thursdays, so i'd like to set the formula at her row level for NETWORKDAYS to add Mondays and Fridays to the already excluded Saturdays and Sundays.

The formula I have for NETWORKDAYS is basic:

=NETWORKDAYS([Start Date]18, [End Date]18)

for calculate networkdays between two dates on row 18

How could I amend this cell formula to count networkdays that fall outside of Tuesdays, Wednesdays, Thursdays??

It could either work directly as adjusted cell formula or reference separate cells that highlight the work days of an individual and apply the cell reference to the formula.

Any help would be much appreciated.

Many Thanks

Paul

Genevieve P.

Hi @prjessy

NETWORKDAYS is configured to a 5 day work week with Saturday and Sunday excluded, as you've found. The only way to exclude other days/dates from the count is by adding these as holiday dates at the end of the formula:

NETWORKDAYS( start_date, end_date, holidays)

If you have Project Settings enabled on this sheet with Dependencies, the function will also take into consideration the Holidays you've set for the sheet as a whole.

See NETWORKDAYS Function for more detail.

In this instance, what I would do is have a column set up either on a reference sheet or in this current sheet that lists out every Monday and Friday for the rest of the year. Then you can use an IF statement to check who the Staff is listed as, and if it's a T/W/Th staff member, use the NETWORKDAYS that excludes M/Fr, like so:

=IF([Staff Name]@row = "Genevieve P.", NETWORKDAYS([Start Date]@row, [End Date]@row, {Genevieve Holidays}), NETWORKDAYS([Start Date]@row, [End Date]@row))

Cheers,

Genevieve