Calculate Business Hours

HI ,
How can i calculate the business hours from a timestamp(Not from Activity Table).?

Lets say i want to include all the records between Monday 8am untill Friday 5pm.Not sure if there is a way currently.

Thank You,
Sravan

Hi Sravan,

Welcome to the Celonis Community :slight_smile:

If I understand you correctly you want to filter records to exclude those for which a particular timestamp doesn’t lie within business hours?

I’m a little confused what you mean business hours. I see two possible interpretations of what you might mean: A) Monday 8am - 5pm, Tuesday 8am - 5pm, …, Friday 8am to 5pm, Or B) Monday 8am to Friday 5pm.

In other words should a time stamp on Tuesday at 11pm be included or not?

I’ll just give you the filter code for both options.

For Interpretation A:

FILTER DAY_OF_WEEK(“Table”.“date column”) >= 1 AND DAY_OF_WEEK(“Table”.“date column”) <= 5;
FILTER HOURS(“Table”.“date column”) >= 8 AND HOURS(“Table”.“date column”) < 17;

By way of explantion DAY_OF_WEEK returns the weekday of the time stamp as an integer. Its worth being aware that monday is 1, saturday is 6 and sunday is 0. HOURS returns the hour part of the time stamp in 24 hours time. So HOURS(“Table”.“date column”) < 17, means the timestamp must be before 5pm.

For interpretation B)

FILTER DAY_OF_WEEK(“Table”.“date column”) != 0;
FILTER DAY_OF_WEEK(“Table”.“date column”) != 6;
FILTER NOT ( DAY_OF_WEEK(“Table”.“date column”) = 1 AND HOURS(“Table”.“date column”) < 8);
FILTER NOT ( DAY_OF_WEEK(“Table”.“date column”) = 5 AND HOURS(“Table”.“date column”) >= 17);

The first two filters exclude Sundays and Saturdays respectively. The third filter excludes timestamps that are on Mondays before 8 am. The forth filter excludes timestamps on Fridays after 5pm.

Note: Filters only include cases for which all conditions return true but as it is often easier to define which case to exclude than those to include, the “NOT” operator is used to reverses the true and false given by the condition written immediately after it.

Does this answer your question? If not feel free to write back.

Best wishes,

Calandra

1 Like

That was Perfect.Thank You!

I was looking for interpretation B.