I am looking for some assistance with using the networkdays function. I have some data from our hire system that gives the Hire Start & Hire End dates, and I need to calculate the number of days between the two dates to return two results. I first need the Total Hire period in days regardless of when it started or ended, and secondly, the number of days that the hire was active in the current reporting period, which I specify in two cells on the sheet. The problem comes when I have to take account of some conditions. Firstly, the Hire End date is sometimes blank if it is an ongoing hire - in this case the calculation A should end on the Report End date. Secondly, sometimes the hire falls totally before the reporting period - in this case the Total Hire period should return the number of working days, but the Hire Days this Period should be set to 0. Thirdly, there are also some occasions where the hire is a future hire, ie the Hire Start date is after the current reporting period - in this case both columns should be set to 0 (not minus). Finally, sometimes there are error corrections from the hire system where the Hire End is before the Hire Start - in this case both columns should be set to 0 as well.

I know how to use networkdays, but I've tried various combinations of nested IF statements and I can't seem to get them to take account of all my conditions. To show what I mean I've attached a screenshot with some sample data. The columns in red are the two calculations I need to return. In this example, I have shown the values that *should* be returned in red, these are not what I actually get however.

Hire Dates Calculation.jpg

Thank you in advance for your assistance.

Martyn