Calculating Store Hours

[TR]

I have a list of sample data with store hours for mon-fri, sat and sun. My goal is to calculate the open hours for mon-fri, sat and sun then sum the hours for a weekly total. I know this should be easy... however, since the store hours are provided dynamically I want to be sure to catch any abnormalities in formatting as you will notice in the sample set.

Thanks in advance!

[anthony]

Without seeing your data, you could use the date format to pull out the day and then use a filter with if statement or join+lookup table to split out by m-f and s/s and summarize.

[TR]

Thanks Anthony, I did attach a sample .yxdb file for review and here's a screenshot:

RecordID 1 is an example of junk-in, so I can't assume all data will be perfectly formatted as the rest (there will be :30 minute times as well.).

Thanks!

[JohnJPS · Accepted solution]

I solved this by basically converting the given hour signatures to DateTimes, then used DateTimeDiff in simple calculations to get the weekly hours .  (See attached).  Hope that helps - there's a fairly complex MultiField formula to convert to datetimes.

[TR]

@JohnJPS very slick way of calculating the store hours! Thank you for this solution, it will come in handy.

Best,

Todd