Need explanation on one of my formula

[VBABEGINER]

=(NETWORKDAYS($AG2,$AH2,Lookup_table!$J$2:$J$10)-1)*("19:00"-"7:00")+IF(NETWORKDAYS($AH2,$AH2,Lookup_table!$J$2:$J$10),MEDIAN(MOD($AH2,1),"19:00","7:00"),"19:00")-MEDIAN(NETWORKDAYS($AG2,$AG2,Lookup_table!$J$2:$J$10)*MOD($AG2,1),"19:00","7:00")

here AG and AH contains date column
from J2 to J10 - holiday list

can anyone please explain me how it is working exactly.. appreciate in advance

 

[GlennUK]

It calculates the number of working hours from the start date/time to the end data/time ... within the working day windows of 7:00 to 19:00. So full days are 12 hours ... any holiday days are skipped (not included). The Median parts adjust the full day result depending on start-time on first day and end-time on last day.

 

[GlennUK]

By the way, the result is an Excel time serial number, so the cell would need to be formatted as [hh]:mm to be seen correctly.

 

[VBABEGINER]

Hi Glenn, Thanks fro reply. I'm still not understand explanation. That i will try to read slowly slowly to understand.

But could you please tell me about your 2nd reply. Then what it should be?

 

[GlennUK]

Hi Glenn, Thanks fro reply. I'm still not understand explanation. That i will try to read slowly slowly to understand.

But could you please tell me about your 2nd reply. Then what it should be?

The second reply is to explain how to format the result ... cell formatting, Number, Custom.

 

[VBABEGINER]

bounce

 

[GlennUK]

bounce

"bounce"?

 

[VBABEGINER]

bounce means in cricket when bowler throws ball to batsman which goes from top of head.. so i got bouncer.. still struggling to understand.

 

[GlennUK]

bounce means in cricket when bowler throws ball to batsman which goes from top of head.. so i got bouncer.. still struggling to understand.

Part 1: this part of the formula:
(NETWORKDAYS($AG2,$AH2,Lookup_table!$J$2:$J$10)-1)*("19:00"-"7:00")
gets the number of working days for the range, and multiplies by 12 hours, to give the number of working hours covered by the date range. The result will be best viewed when you format the cell with a number format of [hh]:mm

Part 2: adjusting the result for start time and end time: this part of the formula:
+IF(NETWORKDAYS($AH2,$AH2,Lookup_table!$J$2:$J$10),MEDIAN(MOD($AH2,1),"19:00","7:00"),"19:00")
adds on the end time
... and this part of the formula:
-MEDIAN(NETWORKDAYS($AG2,$AG2,Lookup_table!$J$2:$J$10)*MOD($AG2,1),"19:00","7:00")
takes off the start time. So you have, for example 15:30 - 10:30 ... (which is 5 hours) added on when the end time of the finishing day is 5 hours after the starting time of the starting date. The MOD function gives the fractional part of the date ... i.e. the time. The MEDIAN function gives the middle time of 7:00 19:00 and whatevertime (whatever time you want to test) ... this is in effect forcing any time pre-7:00 to round up to 7:00, and also forcing any time after-19:00 to round down to 19:00.

Is that clearer?