Validate Open Close Times

Question

Hello All -

I have a file containing various open/close times for customers. I want to compare the earliest and latest times for each day of the week and determine if the time that they are open each day is less than 4 hours. This would be simple enough (e.g. I could use the DateTimeDiff formula) if there were only two sets of times (e.g. Monday AM & Monday PM), but there are actually four sets of times. In addition to that, there is no consistency in the data:

Example

Customers could have their available times setup in various formats:

MondayMonday2Monday3Monday4ResultLT 4 Hrs9:00:0018:00:000:00:000:00:009:00:00N9:00:000:00:000:00:0011:00:002:00:00Y0:00:000:00:009:00:0012:01:003:01:00Y9:00:0011:00:0014:00:0018:00:006:00:00N

Additionally, I want to obviously apply the same logic to Monday, for all of the other days of the week AND ideally not have to write out a ton of formulas if possible.

Let me know your suggestions.

Thanks in advance!

kathleenmonks · Accepted Answer

Hi @mmaziere,

See my attached workflow and screenshot. I assigned each row an ID corresponding to the day number. Then I transposed the data and found the min and max value for the time and performed the calculation of the difference. Some additional steps needed include converting from string to datetime, filtering out the 0:00:00 values, and removing the number from the day name. If you have additional columns for the other days of the week, you'll just have to check the box for them to be included in the transpose, but it should otherwise work for every day of the week.