Calculate hours for an event when it's run for a certain amount of hours over two days

If I'm running a two day event which starts at 10:00 and ends at 14:00 on both days how do I calculate the total duration of the event to be 8hrs?

When I use this formula it's calculating it as start time on the first day to end time on the second day when all I really want is two days multiplied by 4hrs.

=IFERROR(((VALUE(LEFT([EVENT END TIME]@row, FIND(":", [EVENT END TIME]@row) - 1)) + VALUE(RIGHT([EVENT END TIME]@row, 2)) / 60) + ([EVENT END DATE]@row - [EVENT START DATE]@row) * 24) - (VALUE(LEFT([EVENT START TIME]@row, FIND(":", [EVENT START TIME]@row) - 1)) + VALUE(RIGHT([EVENT START TIME]@row, 2)) / 60), "")

If I use this formula to convert the start and end times and then use the NETDAYS formula I get the right answer to a degree as it only likes to give a reults in a whole number and I need it to include hours and minutes.

=VALUE(LEFT([EVENT START TIME]@row, FIND(":", [EVENT START TIME]@row) - 1)) + IF(CONTAINS("p", [EVENT START TIME]@row), IF(VALUE(LEFT([EVENT START TIME]@row, FIND(":", [EVENT START TIME]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([EVENT START TIME]@row, FIND(":", [EVENT START TIME]@row) - 1)) = 12, -12))

=VALUE(LEFT([EVENT END TIME]@row, FIND(":", [EVENT END TIME]@row) - 1)) + IF(CONTAINS("p", [EVENT END TIME]@row), IF(VALUE(LEFT([EVENT END TIME]@row, FIND(":", [EVENT END TIME]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([EVENT END TIME]@row, FIND(":", [EVENT END TIME]@row) - 1)) = 12, -12))

=NETWORKDAYS([EVENT START DATE]@row, [EVENT END DATE]@row) * ([END TIME]@row - [START TIME]@row)

Find more posts tagged with

Accepted answers

Paul Newcome

So what you need to do is add on to each so that the minutes are output as a decimal. You are also using an overly complicated formula since your times are already in 24 hour format. Try this instead:

=VALUE(LEFT([Event Start Time]@row, FIND(":", [Event Start Time]@row) - 1)) + (VALUE(RIGHT([Event Start Time]@row, 2)) / 60)

Do the above for the event end time as well and then use your NETWORKDAYS piece.

All comments

Paul Newcome

Are you able to provide some screenshots for context?

Samantha McDonald

Hi @Paul Newcome hopefully this makes better sense!

This gives me 28hrs:

This gives me 8hrs which is correct but if the number isn't a whole one, then it doesn't take this into account and I need to be able to report the minutes too. The second line is an example of that as it reports 6hrs but I want it to be 5.5hrs

Paul Newcome

What formulas are you using for the [END TIME] and [START TIME] columns?

Samantha McDonald

Paul Newcome

=VALUE(LEFT([Event Start Time]@row, FIND(":", [Event Start Time]@row) - 1)) + (VALUE(RIGHT([Event Start Time]@row, 2)) / 60)

Do the above for the event end time as well and then use your NETWORKDAYS piece.

Samantha McDonald

That worked a treat, thank you so much! Merry Christmas!

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.