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
-
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
-
Are you able to provide some screenshots for context?
-
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
-
What formulas are you using for the [END TIME] and [START TIME] columns?
-
=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))
-
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.
-
That worked a treat, thank you so much! Merry Christmas!
Help Article Resources
Categories
Check out the Formula Handbook template!