If criteria is xxx, duration is zero

Nancy Skoulphong

Dear Smartsheet community,

I have a sheet for staff to report time off. 

I have this formula to calculate the net working day(s) between begin date and end date.

=NETWORKDAY([Begin Date]@row, [End Date]@row)

However there will be occasion when staff will not be taken time off. Therefore, I want to add where column Absence Name is No Leave Taken, then the result is zero.

Thank you!

 

BernardW

You can use the IF function for this.  The documentation on how to use it is here.  https://help.smartsheet.com/function/if

Function: IF(logical_expression, value_if_true, [value_if_false])

Example: IF([Absence Name]@row = “No Leave Taken”, 0, <Insert your NETWORKDAY function>)

I am curious, however, why you have people reporting time off in the time off table when they aren’t taking time off.

Cheers

Bernard

Andrée Starå

Hi,

Try something like this.

=IF([Absence Name]@row = "No Leave Taken"; 0; NETWORKDAY([Begin Date]@row; [End Date]@row))

The same version but with the below changes for your and others convenience.

=IF([Absence Name]@row = "No Leave Taken", 0, NETWORKDAY([Begin Date]@row, [End Date]@row))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Nancy Skoulphong

Thank you, Bernard. We want staff to report the month when no time off is taken for analytic and reporting purposes. 

Nancy Skoulphong

Thank you, Andree!

Andrée Starå

Happy to help!