recalculate dates to working days / office hours

RinkaDink

Dear Dataiku Team & Community,

the software is fantastic and just need to get a little more familiar.

With one of my first projects I need check if a given date is weekend (sat / sun) and out of office hours.

1. If Sunday then set Friday @ 16pm

2. If Saturday then set Friday @16pm

3.If weekday and before 10am then prior day @16pm (but not allowed to be weekend see 1+2)

4.if weekday and after 17pm then same day @16pm

I was trying with formular in receipe "prepare" but as said could need a helping hand with the formular.

I got how to substract days but then failed when trying to set the time with dateadd to 16pm.

Sarina

Hi @RinkaDink
,

Can you please attach a screenshot or a couple lines of what your input dataset and corresponding "office hours" column looks like? It would also be helpful to see what you expect your output column to look like after applying the formula step for a couple of example rows to be of the most help.

Thank you!
Sarina

Marine

Hi @RinkaDink
,

The operations inc and trunc from the date functions will help you. Have a look at the corresponding documentation.

Right now, I would create a prepare recipe and do the following:

A. Parse the date column (if necessary)

B. Add the "extract date" step to retrieve the day of week and the hour.

Note that the "day of week column" will contain numbers from 1 to 7.

C. Create a formula to perform your point 3 or 4. For your point 3, it could look similar to this :

if(val("day of week") < 6 && (hour<11 ), inc(trunc(inc(val("Last Activity Date_parsed"), -1, "days"), days), 16, "hours"), val("Last Activity Date_parsed"))

Here are some explanations :

- If week days => val("day of week") < 6

- if out of office hours => (hour<10)

- prior day @16pm => inc(trunc(inc(val("Last Activity Date_parsed"), -1, "days"), days), 16, "hours")

D. Create a new formula on top of the resulting column of my step C to perform your points 1 and 2. Hence, you'll make sure that there will be no week ends at the end =>

For example, your statement "If Sunday then set Friday @ 16pm" results in:

if(val("day of week") ==7, inc(trunc(inc(val("Last Activity Date_parsed"), -2, "days"), days), 16, "hours"), val("Last Activity Date_parsed"))

If you want to perform more advanced tasks on dates, I'd advise you to install the "Time series preparation" plugin and try the resampling recipe. This recipes can convert dates from a daily frequency to a "business day" frequency. Our dedicated tutorial will show you how to make the most of the time series preparation plugin.