Calculating the difference in hours between two time stampst excluding weekends

[esssti]

Hi All,

I need to calculate the difference in hours between two timestamps. the format of the cells is YYYY.MM.DD HH:MM:SS, but weekends have to be excluded.
The problem is that the first timestamp can be also on a weekend.
In case the first timestamp is on a friday at 2pm and the second is monday 2pm that should be 24hrs.
In case the first timestamp is for example on saturday any time, second timestamp also on monday at 2pm, that should be 14 hrs.

Is there any formula to get this result?
Thanks in advance!

 

[barry houdini]

Hello esssti, welcome to MrExcel

If the start time is in A2 and end time in B2 try this formula in C2

=NETWORKDAYS(A2,B2)-1+MOD(B2,1)-NETWORKDAYS(A2,A2)*MOD(A2,1)

Custom format C2 as [h]:mm - note square brackets

 

[esssti]

Hi Barry,

Thanks for the quick help. I tried the formula and in case the start date and the end date are different days, the formula works fine, but in case they are same day, only the hours and mins differ i get a negative, and not correct number. Do you have a solution for that?

Thanks,
Eszter

Hello esssti, welcome to MrExcel

If the start time is in A2 and end time in B2 try this formula in C2

=NETWORKDAYS(A2,B2)-1+MOD(B2,1)-NETWORKDAYS(A2,A2)*MOD(A2,1)

Custom format C2 as [h]:mm - note square brackets

 

[barry houdini]

I can't replicate that problem unless both dates are on a Saturday or Sunday.....but I assumed that the second timestamp wouldn't be at the weekend....can it be?

Otherwise can you give an example where you don't get the correct result. If I have 21st Feb at 14:00 in A2 and 21st Feb at 18:00 in B2 the formula gives me 4:00 as expected

 

[esssti]

Hi Barry ,

For some reason seems my answer did not come through.

Yes, unfortunately it can happen that both dates fall on a weekend day. What i am trying to achieve with this formula is if the troubleshooting was completed on time or not. the team who does the troubleshooting is not working weekends, but in some cases remote solution can be applied hence the weekend days.

We have two columns, one for the length of the troubleshooting and one to check if it is within the timeframe, so weekend hours can skew the results and negatively influence the performance of the team in the statistics.

now what i am thinking is to leave the length as it was and 'manipulate' somehow the on time column..
- lets sat the troubleshooting target time frame is 10 hours, the length of the troubleshooting was 28 hours and the formula would deduct the weekend hours and check again if the adjusted length fits the timewindow.

Do you have a formula for that or any better solution?

Your help is much appreciated.

thanks in advance

 

[barry houdini]

A small addition to the formula should allow it to work even if both dates are at the weekend, i.e.

=NETWORKDAYS(A2,B2)+NETWORKDAYS(B2,B2)*(MOD(B2,1)-1)-NETWORKDAYS(A2,A2)*MOD(A2,1)