Mar 5, 2014

I have a formula that looks like this:

=12*NETWORKDAYS(C2,D2)-12+IF(NETWORKDAYS(D2,D2),MEDIAN(MOD(D2,1)*24,6,18),18)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1)*24,6,18)

This formula calculated the time between two dates (Start date in C2, End Date in C3) between the hours of 06:00 and 18:00 on weekdays. This is so I can work out how long an incident has spend within the group at the time when our service should be operating.

I was given this formula by someone on this forum a while back and i was new to excel and formulas so I just took it at face value since it worked. Now I want to disect it so I can use it in other situations where similar calculations are needed. I want to try to break it up into easier to understand chunks to try to understand what it is I need to change in order to do these related calculations.

NETWORKDAYS(D2,D2)

Is this trying to distinguish if D2 is a weekday?

MOD(D2,1)*24 Is this not the same as (1-D2)*24

NETWORKDAYS(C2,C2)*MOD(C2,1) Is this not the same as: IF(weekday(C2)>5,(C2-1)*7,0)

IF(NETWORKDAYS(D2,D2),....,....) What is the comparison here? if NETWORKDAYS(D2,D2) = 0 or 1?

Is this equivalent?

=12*(

NETWORKDAYS(C2,D2)-12

+IF(NETWORKDAYS(D2,D2),MEDIAN(MOD(D2,1)*24,6,18),18)

-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1)*24,6,18)

)

View 1 Replies
View Related