Netdays calculation with optionnal date column

Julien Lopez

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

Melissa Yamada

@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

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

Julien Lopez

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

@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))))

Julien Lopez

thank you so much!!