Office hours

Hi Arjan,

Sounds good. Please feel free to share that part of the project - it would be interesting to see how you merged the dataset with the sequence.

We have custom blocks Interval join and Inequality join to help with many to many scenario.

Thanks Paola

We have solved in now with a reference table and a many to many relation, comparing the start and end date with the reference data and adding the lost hours when the dates are equal or one day before.

Regards

Arjan

You could still use NETWORKDAYS to evaluate number of working days between Start date and End date (multiplied by number of working hours in a day) , then deduct 'hours lost' due to work starting after 09:00am or finishing before 17:00 per each engagement. This will work if your data is 'clean', so each job's start time is reliably before the end time.

Note - NETWORKDAYS is inclusive of start/end day, so today start > today end = 1 day; Monday > Friday is going to be 5 days.

Demo File attached.

"Lost hours" calculation per pair:

DATEDIFF(

TEXTTODATE(

DATETOTEXT(

[WStart],"dd/MM/yyyy")+" 09:00"), 

[WStart], 

"hour")

+

DATEDIFF(

[WEnd],

TEXTTODATE(

DATETOTEXT(

[WEnd],"dd/MM/yyyy")+" 17:00"), 

"hour")

You are correct - NETWORKDAYS  is counting non-weekend days between 2 dates, so result will be an integer.

For your scenario you might try to join the values with a date sequence, containing day start time - day end working time.

It would be good to understand why in your dataset sometimes the left value and sometimes the right date/time is the earlier slot? (resulting in negative datediff )

Is my previous comment received? I cant see it. Hereby the screenshot I mentioned

Hi Paola

Thanks for replying.

I have tried this but this is not sufficient unfortunately.

I have attached a screenshot of our tabel. It has DateTime1, DateTime2, DateDiff and WorkingDays.

As you can see, WorkingDays returns an integer. DateTime1 and DateTime2 on the same day returns 0.

In addition it returns 1 when on two following days.

It doenst take working hours into account.

Is there another function we can use?

Hi Arjan,

You can use NETWORKDAYS function to calculate number of working days between the two dates, then easily multiply with 8.

Please remember that formula is excluding the weekend days, so if you need it to accommodate national holidays - you may need to add this manually.

For more formula advice - please use our interactive formula guide (accessible from the formula builder interface) or ask the Omniscope Ninja!

https://visokio.com/ninja