Calculating lost hours between dates

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)

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.