Calculating lost hours between dates

Luis Orosco

hello

I wrote a formula to calculate the hours lost on a product cell due to down time, it meant to calculate 8 hours for a day but it counts 16 hours if the cell stops for a day as it counts starting date and closing date as 2 different days instead of just one single day down. I need to count 8 hours is the event closes the same day and substract 8 hours if the starting and the closing date are different.

I can write on Excel but i seem to have a hard time picturing it in Smartsheets.

=IF(ISDATE([Closing Date]@row), NETWORKDAY([Event IDate]@row, [Closing Date]@row) * 8)

Genevieve P.

Hi @Luis Orosco

If I'm understanding you correctly, you want the formula to show 8 if the Start and End date are the exact same (so 1 day), but then you want any other task length to be -8 so that if a task Starts Today but Ends tomorrow it's seen as 1 as well (versus 2).

If so, try adding another IF statement to your formula to account for the one scenario where you want 8 to show up:

=IF(ISDATE([Closing Date]@row), IF(NETWORKDAY([Event IDate]@row, [Closing Date]@row) = 1, 8, (NETWORKDAY([Event IDate]@row, [Closing Date]@row) * 8) - 8))

Let me know if that makes sense and gives you the output you're looking for!

Cheers,

Genevieve