Need formula understanding

[VBABEGINER]

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

can anyone pls explain and understand me how this formula working..

 

[Rijnsent]

Hi VBABeginner,
chopping your formula into bits. I'm assuming AF2 and AG2 have dates in them.

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

Line 1: This is basically calculating a number of days worked. The first block (NETWORKDAYS) is multiplied by 0,5 (12 hours from 7 to 19 divided by 24 hours in a day). It calculates the net workdays in the period between two days (AF2 and AG2) given the holidays in Lookup_table!J2:J10.
For NETWORKDAYS: it includes the days put into it: so if AG2 is equal to AF2, it will return 1. One exception: if AG2 is in the list of holidays, then it will return a 0. The MOD makes sure you only take the hour part of your date/time value. The MEDIAN makes sures it's between 7h and 19h.
So line 2 determines the hour of AG2, limited by 19h and 7h, line 3 does the same for AF2. That should give the number of hours between AG2 and AF2, given a workday from 7 to 19 and the holidays int he Lookup_table.

Hope that helps,
Koen