# Need formula understanding

#### VBABEGINER

##### Well-known Member
Code:
``=(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..

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Rijnsent

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

Code:
``````=(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

Replies
8
Views
118
Replies
0
Views
209
Replies
26
Views
386
Replies
0
Views
197
Replies
9
Views
75