We need to calculate the numbers of office hours between 2 dates.

Is there a formula in Omniscope Evo we can use?

Many thanks for your help

Arjan

0 Votes

P

Paola Tomei posted
7 days ago
AdminBest Answer

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.

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.

0 Votes

A

Arjan Meesterposted
5 days ago

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

0 Votes

P

Paola Tomeiposted
7 days ago
AdminAnswer

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.

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 )

0 Votes

A

Arjan Meesterposted
9 days ago

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

0 Votes

A

Arjan Meesterposted
9 days ago

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?

0 Votes

P

Paola Tomeiposted
14 days ago
Admin

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!

HI,

We need to calculate the numbers of office hours between 2 dates.

Is there a formula in Omniscope Evo we can use?

Many thanks for your help

Arjan

0 Votes

Paola Tomei posted 7 days ago Admin Best Answer

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")

Attachments (1)

Timesheet.ioz

1.24 MB

0 Votes

7 Comments

Paola Tomei posted 5 days ago Admin

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 joinandInequality jointo help with many to many scenario.0 Votes

Arjan Meester posted 5 days ago

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

0 Votes

Paola Tomei posted 7 days ago Admin Answer

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")

Attachments (1)

Timesheet.ioz

1.24 MB

0 Votes

Paola Tomei posted 8 days ago Admin

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 )

0 Votes

Arjan Meester posted 9 days ago

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

0 Votes

Arjan Meester posted 9 days ago

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?

0 Votes

Paola Tomei posted 14 days ago Admin

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

0 Votes

Login or Sign up to post a comment