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!

Find more posts tagged with

Human Resources

Education

Formulas

Comments

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

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!

Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…