DateTime Functions: Identify Time Within Set Parameters

[jessfarthing]

Hi,

I currently have to identify hours worked between 2 times of day from a very large set of timesheet data and in a few different iterations.  I wanted to ask what the most efficient ) way to write the relevant Date/Time expression that would capture the hours associated with the following criteria:

Hours worked on a weekday prior to 7am and finishing after 9pm

Hours worked on a Saturday prior to 7am and finishing after 6pm

Hours worked on a Sunday prior to 9am and finishing after 6pm

While it seems a bit simple prima facie,  I have to write over 50 Date/Time criteria, all in a similar fashion, hence wanting to ensure its the most optimised so I can potentially macro this later.  Additionally, is there any way to write this that does not involve appending the time ranges above to each timesheet entry?

Sample data is attached.

[DavidP · Accepted solution]

Is it something like this you're looking for?

[RolandSchubert]

Hi @jessfarthing ,

you could use a list of categories (day of week class, start and end time), join it to your data and filter the rows fullfilling the criteria.
I've attached a sample workflow. What do you think?

Best,

Roland

[jessfarthing]

Hi!

What's missing from this is summing the hours that fall within the parameters (i.e. how many hours were before 7am or after 9p?)