MEDIAN, EOMONTHS and NETWORK DAYS Formula

[wesselsV]

Hi,

I'm trying to spread the cost of a resource between two dates. The start and finish dates vary and may fall within the same week or run across several months. I've managed to stumble across a formula I've found elsewhere and tweaked it to suit the format of my table but I'm struggling for it to accurately take account of network days and actual work days based on a five day week.


=$D6*(H$1=MEDIAN(EOMONTH($B6,-1)+1,EOMONTH($C6,0),H$1))*(MIN($C6,EOMONTH(H$1,0))-MAX($B6,H$1-1))

https://1drv.ms/x/s!As-9OWf6QipWg05iIjaIL8wyN9Lm

In above file and on Row 6 for September there should be £5,000 cost spread and in Row 7 there should be a cost spread of £20,000 for September and £8,000 for October all based on actual work days. However this is higher as it is strictly based on the start and finish dates.

Can anyone kindly assist?

Thanks

 

[iggy_]

yea looks like you need to modify just a little bit. Try this in cell F4 and then copy to the right and down:

=$D4*(F$1=MEDIAN(EOMONTH($B4,-1)+1,EOMONTH($C4,0),F$1))*NETWORKDAYS(MAX($B4,F$1),MIN($C4,EOMONTH(F$1,0)))

 

[steve the fish]

I think this will work as well:

=$D4*MAX(NETWORKDAYS(MAX($B4,F$1),MIN($C4,EOMONTH(F$1,0))),0)

 

[iggy_]

And thats a lot easier to read Steve!