How to calculate working hours?

[Mer333]

Hello again guys!

Today I have a really unusual task. =)

I have 2 date columns - start date and finish date accordingly.

I want to calculate a mathematical difference between these dates in a third column, but without taking free (non working) time into consideration.

For instance, if start date is something like 3 pm friday and finishing date is 12 am monday then having an eight-hours working day we will get 7 working hours.

If it's too difficult as a last resort we could just not count Saturday and Sunday.

 

[iliace]

I want to calculate a mathematical difference between these dates in a third column, but without taking free (non working) time into consideration.

So what are the working days, or rather, what defines the non-working time?

For instance, if start date is something like 3 pm friday and finishing date is 12 am monday then having an eight-hours working day we will get 7 working hours.

By standard (US) definition of a working day, it's 9am-5pm, Mon-Fri. What definition are you going by here? 12am Monday is outside of working hours as far as I know. If you mean "12pm" instead, then it's only 3-5 on Friday (2hrs) and 9-12 on Monday (3hrs) for a total of 5 hours.

If it's too difficult as a last resort we could just not count Saturday and Sunday.

In what way are we counting them, were we to do so?

I'm unclear. Sorry if I come off obtuse.

 

[Mer333]

So what are the working days, or rather, what defines the non-working time?

I suppose it should be customizable parameter.

By standard (US) definition of a working day, it's 9am-5pm, Mon-Fri. What definition are you going by here? 12am Monday is outside of working hours as far as I know. If you mean "12pm" instead, then it's only 3-5 on Friday (2hrs) and 9-12 on Monday (3hrs) for a total of 5 hours.

Yeah, here is a little mistake from me. =) The fact is that non-working hours is very variable especially if we will take official holidays into consideration.

In what way are we counting them, were we to do so?

Maybe I was a bit inaccurate in the first description. If we calculating difference between two dates, than Saturday and Sunday counting by default.

I'm unclear. Sorry if I come off obtuse.

It's ok, we all people here. =)

 

[scottsen]

Think I am with iliace here, in that we would need to firm up the requirements or... something.

If you were asking a specific question like "I have a table that stores all of our holidays, and we always exclude Sat/Sun... how do I calculate the number of hours between two dates..." I think we could help.

eg, we could count the number of rows in a holidays table that lie in between the Start/End in your "Working" table.

=CALCULATE(COUNTROWS(Holidays), FILTER(Holidays, Holidays[Date] >= Working[StartDate] && Holidays[Date] <= Working[EndDate]))

And subtract off that #... times 8 or 24, or whatever, from the End-Start calc?

 

[Mer333]

=CALCULATE(COUNTROWS(Holidays), FILTER(Holidays, Holidays[Date] >= Working[StartDate] && Holidays[Date] <= Working[EndDate]))

And subtract off that #... times 8 or 24, or whatever, from the End-Start calc?

Yeah, you are genius. It's totally solved excluding holidays and also Sat/Sun if we have all of them also in Holidays[Date] column. So after this operation we have hours of working days remain. But question of excluding non-working hours from working days is still open.

 

[scottsen]

You could certainly just modify the filter to exclude weekends... eg:

=CALCULATE(COUNTROWS(Holidays), FILTER(Holidays, Holidays[Date] >= Working[StartDate] && Holidays[Date] <= Working[EndDate] && Calendar[ShortDayOfWeekName] <> "Sat"))

For the non-working hours, realize that dates are just stored as a number... with "1" representing a full day. So, Feb 14 3:00pm to Feb 14, 3:00pm should be just 0.5. So, this should all be just a straight "math problem". Subtract, done.

 

[Mer333]

Could you please be a little more specific? How can we subtract non-working hours? If, for example, we have a working hours from 9am to 5pm.

 

[scottsen]

Go try it, you will see what I mean.

Create a sample table with two columns: 1/4/2010 5pm and 1/5/2010 7pm... then a calculated column that subtracts them. You will get 1.08. Which is your total time in days.

And indeed =([Column2]-[Column1])*24 will give you... 26. As in hours.

 

[Mer333]

Go try it, you will see what I mean.

Create a sample table with two columns: 1/4/2010 5pm and 1/5/2010 7pm... then a calculated column that subtracts them. You will get 1.08. Which is your total time in days.

And indeed =([Column2]-[Column1])*24 will give you... 26. As in hours.

Yeah... But how to figure out how many non-working hours there are in each period if they all have different start and end dates, in purpose to subtract them?