Calculating lost hours between dates


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.
    Genevieve P. Employee Admin

    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!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!