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

Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?