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

Samantha McDonald

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)

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.

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

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

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.

Samantha McDonald

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

Paul Newcome

Happy to help. 👍️