Need to calculate difference between 2 dates, sans W/E and holidays, with result in months and days
I am developing a Project Tracking Workbook and need to calculate the difference between START DATE (Col N) and END DATE (Col P) as the TOTAL PRODUCTION TIME (Col R) in months and days.
I have tried two formulas, as follows:
=IF($N10<>"",IFERROR(DATEDIF($N10,$P10,"ym")&" months "&DATEDIF($N10,$P10,"md")+1&" days", "No 'End Date' Entered"),"")
This one works, but does not remove W/Es or holidays. It is in cells R4 and R5 of the attached screenshot.
=IF($N12<>"",IFERROR(NETWORKDAYS($N12,$P12,M116:M1 23)&" months "&NETWORKDAYS($N12,$P12,M116:M123)&" days", "No 'End Date' Entered"),"")
This is my poor attempt to incorporate NETWORKDAYS, which kind of works but returns only the number of days in both counts. It is in cell R6 of the attached screenshot.
I have tried several methods to convert the (first) calculation result into months with no luck.
Any suggestions?
|