Netdays calculation with optionnal date column

Hi

i'm looking for a formula to calculate the netdays between 2 dates. but i have 3 dates in the equation. a date column (date 1) whit references from another sheet and a manual one (date 2) to overwrite the first one. the third date is the "today" date (date 3).

so i'm looking for something like: netdays between date 1 and date 3 if there is NO date 2, if there is a date 2 then calculate the netdays between date 2 and date 3.

thank you for your help

Best Answers

  • Melissa Yamada
    Melissa Yamada ✭✭✭✭✭
    Answer ✓

    Hello @Julien Lopez ,

    I'd be happy to help!

    You'll use a nested IF for this formula.

    1. netdays between date 1 and date 3 if there is NO date 2 (First IF statement: =IF(AND([Date 1]@row <> "", [Date 2]@row = ""), NETDAYS([Date 1]@row, [Date 3]@row)))
    2. if there is a date 2 then calculate the netdays between date 2 and date 3 (Second IF statement: IF(AND([Date 1]@row = "", [Date 2]@row <> ""), NETDAYS([Date 2]@row, [Date 3]@row)))

    Then combining the 2 IF statements will give you the final formula:

    =IF(AND([Date 1]@row <> "", [Date 2]@row = ""), NETDAYS([Date 1]@row, [Date 3]@row), IF(AND([Date 1]@row = "", [Date 2]@row <> ""), NETDAYS([Date 2]@row, [Date 3]@row)))

    You may refer to these articles:

    https://help.smartsheet.com/function/and

    https://help.smartsheet.com/function/if

    Melissa Yamada
    melissa@insightfulsheets.com
    Data made simple, spreadsheets reimagined

  • Melissa Yamada
    Melissa Yamada ✭✭✭✭✭
    Answer ✓

    @Julien Lopez if there is date on both Date 1 and Date 2, it will return a blank/ empty cell.

    If this is the case, you'd need to add another IF statement:

    IF(AND([Hiring Date]@row <> "", [Corrected Hiring Date]@row <> ""), NETDAYS([Corrected Hiring Date]@row, [Start Date Rotation]1))

    Final formula:

    =IF(AND([Hiring Date]@row <> "", [Corrected Hiring Date]@row = ""), NETDAYS([Hiring Date]@row, [Start Date Rotation]1), IF(AND([Hiring Date]@row = "", [Corrected Hiring Date]@row <> ""), NETDAYS([Corrected Hiring Date]@row, [Start Date Rotation]1), IF(AND([Hiring Date]@row <> "", [Corrected Hiring Date]@row <> ""), NETDAYS([Corrected Hiring Date]@row, [Start Date Rotation]1))))

    Melissa Yamada
    melissa@insightfulsheets.com
    Data made simple, spreadsheets reimagined

Answers

  • Melissa Yamada
    Melissa Yamada ✭✭✭✭✭
    Answer ✓

    Hello @Julien Lopez ,

    I'd be happy to help!

    You'll use a nested IF for this formula.

    1. netdays between date 1 and date 3 if there is NO date 2 (First IF statement: =IF(AND([Date 1]@row <> "", [Date 2]@row = ""), NETDAYS([Date 1]@row, [Date 3]@row)))
    2. if there is a date 2 then calculate the netdays between date 2 and date 3 (Second IF statement: IF(AND([Date 1]@row = "", [Date 2]@row <> ""), NETDAYS([Date 2]@row, [Date 3]@row)))

    Then combining the 2 IF statements will give you the final formula:

    =IF(AND([Date 1]@row <> "", [Date 2]@row = ""), NETDAYS([Date 1]@row, [Date 3]@row), IF(AND([Date 1]@row = "", [Date 2]@row <> ""), NETDAYS([Date 2]@row, [Date 3]@row)))

    You may refer to these articles:

    https://help.smartsheet.com/function/and

    https://help.smartsheet.com/function/if

    Melissa Yamada
    melissa@insightfulsheets.com
    Data made simple, spreadsheets reimagined

  • Hello @Melissa Yamada, thank you for your help

    the formula works well for the first part (Date 1 and Date 3) but the cell is empty when the is a Date 2.

    here is the formula i tried:

    =IF(AND([Hiring date]@row <> "", [corrected hiring date]@row = ""), NETDAYS([Hiring date]@row, [Start date rotation]1), IF(AND([Hiring date]@row = "", [corrected hiring date]@row <> ""), NETDAYS([corrected hiring date]@row, [Start date rotation]1)))

    Date 1: Hiring date

    Date 2: corrected hiring date

    Date 3: start date rotation

    thank you

  • Melissa Yamada
    Melissa Yamada ✭✭✭✭✭
    Answer ✓

    @Julien Lopez if there is date on both Date 1 and Date 2, it will return a blank/ empty cell.

    If this is the case, you'd need to add another IF statement:

    IF(AND([Hiring Date]@row <> "", [Corrected Hiring Date]@row <> ""), NETDAYS([Corrected Hiring Date]@row, [Start Date Rotation]1))

    Final formula:

    =IF(AND([Hiring Date]@row <> "", [Corrected Hiring Date]@row = ""), NETDAYS([Hiring Date]@row, [Start Date Rotation]1), IF(AND([Hiring Date]@row = "", [Corrected Hiring Date]@row <> ""), NETDAYS([Corrected Hiring Date]@row, [Start Date Rotation]1), IF(AND([Hiring Date]@row <> "", [Corrected Hiring Date]@row <> ""), NETDAYS([Corrected Hiring Date]@row, [Start Date Rotation]1))))

    Melissa Yamada
    melissa@insightfulsheets.com
    Data made simple, spreadsheets reimagined

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!