Need formula understanding

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,130
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..
 

Some videos you may like

Excel Facts

Excel Can Read to You
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
Joined
Oct 17, 2005
Messages
1,298
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,753
Messages
5,524,641
Members
409,597
Latest member
Dannydev

This Week's Hot Topics

Top