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)

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!