Calculate Hours between two dates without weekend

[treeppm]

Hi fellows.

I have two dates like following

2019-03-07 21:00
2019-03-18 11:00

I would like to know how many hours between these two dates omitting Saturday and Sunday
Kindly guide me for the formula to get results

Kind Regards

 

[treeppm]

One simple example with result
date format(yyyy-mm-dd HH:MM)
2019-03-07 21:00
2019-03-11 03:00

between these two values the total hours is 30 (without Saturday Sunday).

what is the formula I can use to get the result 30?..

 

[Brombrough]

Hi treeppm,

First format result Cell to [h]

Date cell A1 and B1


formula equals =NETWORKDAYS(A1,B1)-1-MOD(A1,1)+MOD(B1,1)


Enjoy

Hope it helps

 

[treeppm]

Thank you so much for the good help bro., works perfect.
Could you please explain this formula?.. very eager to learn

Kind Regards

 

[Brombrough]

Hi Treeppm,
It’s a bit tricky but first of all, you will have to understand how Excel actually stores/uses (Regardless of the format you see or set) dates and Times.

Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2019 is serial number 43466 because it is 43,466 days after January 1, 1900.
07/03/2019  21:00:00 = 43531
11/03/2019  03:00:00 = 43535


Microsoft Excel stores times as  decimal number between 0 and 0.999988426 given an hour, minute and second value. A result of 0 represents 12:00:00 AM, 0.25 =  06:00:00 AM, 0.50 =  12:00:00 PM, 0.75 =  06:00:00 PM.
07/03/2019  21:00:00 = 43531.875
11/03/2019  03:00:00 = 43535.125


NETWORKDAYS
Returns the number of whole working days between start_date and end_date. Working days exclude weekends


MOD function
Returns the remainder after number is divided by divisor           MOD(number, divisor)


In your case, the Calculation is as follows (Remember that dates are just Numbers)
=NETWORKDAYS(A1,B1)-1-MOD(A1,1)+MOD(B1,1)
NETWORKDAYS(A1,B1)           
Answer:         = 3           number of whole working days
NETWORKDAYS(A1,B1)-1
Answer:         = 2           number of whole working days but we want the difference not the total whole number
MOD(A1,1)
Answer:         = 43531.875 - 43531   which  = 0.875   then Divide this by 1  which =  0.875
MOD(B1,1)
Answer:         = 43535.125- 43535   which  = 0.125   then Divide this by 1  which =  0.125
Therefore your answer =
2 - 0.875 + 0.125 = 1.25               Convert this to a date and you get  01/01/1900 06:00    (1.25 in Numerical terms)
Finally, we can format the cell as [h] which gives the answer as (1 Whole day + ¼ of a day) gives total hours of 30
If you format the cell to [h]:mm   (This would give total hours and minutes your answer would be 30:00
Hope that this is clearer than mud to you.

Hope that this is clearer than mud to you.

 

[treeppm]

Hi Treeppm,
It’s a bit tricky but first of all, you will have to understand how Excel actually stores/uses (Regardless of the format you see or set) dates and Times.

Wow,,,, very neat and clear..,