Calculating Store Hours

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!

StoreHoursSampleData.yxdb

Regex

Output

Preparation

Expression

Accepted answers

JohnJPS

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.

WeeklyHours.yxmd

All comments

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.

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

Calculate Hours

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

WeeklyHours.yxmd

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

Best,

Todd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703