Embedded IF formula in NETDAYS

Options

I am attempting to write a NETDAYS formula dependent on the input from a column on a sheet. I imagine I need to embed an IF formula, but I have had no luck in the format.

Any help would be much appreciated.

Best Answers

Answers

  • Christian Graf
    Christian Graf ✭✭✭✭✭
    Options

    Hello @Jennifer Parins

    Could you be more specific? Do you have images of what you are trying to accomplish?

    Nested If formulas could be what you are looking for. Here is an example of a formula that checks a column for a value and performs a netdays formula based on what it finds.

    =IF(column1@row = "Start", NETDAYS(date1@row, date2@row), IF(column1@row = "End", NETDAYS(Today(), date2@row),""))


    If you share some images of what values you want found, and describe what you want to happen, I could build you an exact formula for your application.


    Hope this helps!

  • Jennifer Parins
    Options

    I am attempting to find the NETDAYS only when the indicated column is "Accident".

    I will look back at your formula and see if I can figure out where my formula went wrong.

    Thanks for your help!!!

  • Christian Graf
    Christian Graf ✭✭✭✭✭
    Answer ✓
    Options

    @Jennifer Parins

    Okay,

    I don't know what date columns you are using, but the following formula should get you close:

    =IF([Type of Incident]@row = "Accident", NETDAYS([date 1]@row, [date 2]@row),"")

  • Jennifer Parins
    Options

    I am still struggling with this equations. I feel that I am not explaining my needs very well.

    When the "Type of Incident" column registers and accident, I want to find the number of days from today to that last accident.

    This is the formula I have and I hope someone will be able to tell me where it is not correct:


  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Jennifer Parins

    Christian's formula above would be the correct structure for an in-sheet formula, but it looks like you're wanting to calculate this across sheets.

    The issue here is that the range {Type of Incident} is looking at your entire column, versus looking at one cell. How will your formula know what row to look at?

    If you just want to check and see if any cell contains "Accident", then you could use a COUNTIF:

    =IF(COUNTIF({Type of Incident}, "Accident") > 0, NETDAYS(MAX({DATE}), TODAY()))

    Keep in mind that if your Max Date in that sheet is not the same as one of your rows with "Accident" then this won't bring back the correct row's data.

    I would suggest creating a formula on the main source sheet, so you can calculate the NETDAYS for each individual row, then you could pull this into a Report instead of a second sheet.

    Cheers,

    Genevieve

  • Jennifer Parins
    Answer ✓
    Options

    Thank You Genevieve. I was struggling with this but your explanation makes perfect sense.

    All of the assistance is amazing; thank you again.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!