Annual cost calculation based on date

**sekharyadav** · 05-12-2013, 03:16 AM

HI All,

Need help to get the formula for calculating cost based on working days in a month (networkdays) based on date we are giving in DOJ column(C)

in attached sheet, given 5/15/2013 in colum C , so before given date & Months value should be "0" and need "May" month remining working days and next months cost .

i used below formula but not working

IF(IF(TEXT($C6,"mmm")=TEXT(F$4,"mmm"),160/30*NETWORKDAYS($C6,F2),0),IF(TEXT($C6,"MMM")<TEXT(F$4,"MMM"),160/30*NETWORKDAYS(F1,F2),0),0)

Thanks,
Sekhar

**watersev** · 05-12-2013, 05:25 AM

hi sekharyadav, option

**daddylonglegs** · 05-13-2013, 06:51 AM

What does 160/30 represent - is that working hours in a day? Try this formula in F6 copied across

=MAX(0,NETWORKDAYS(MAX($C6,F$1),F$2))*16/3

perhaps you need to multiply by the hourly rate in E6 too?