Calculating the difference between two dates

[Morfi]

I've got a column that records the date I receive something and a column that calculates the date it should be responded to (15 working days).

I now want to include a column that works out the number of days to go until the deadline date is reached.

Column A = list of bank holidays from row 2 to row 11
Column B = date of receipt of correspondence
Column C = deadline for response

What formula could I use to calculate how many working days to go until the deadline date?

Thanks

 

[GlennUK]

Have a look at the NETWORKDAYS function. You may need to add-in the Analysis Toolpak if you are using a version of Excel prior to 2007.

 

[Morfi]

I've used the Network days function for another of the calculations on the same spreadsheet and they work ok. Can anyone suggest what the formula might be or can't it be done without analysis toolpak?

Thanks

 

[barry houdini]

If C2 is a future date then try

=NETWORKDAYS(TODAY(),C2,A$2:A$11)

When you reach and pass C2 then that will return a negative number so you might want to control that, e.g. stop it going negative by using MAX, i.e.

=MAX(NETWORKDAYS(TODAY(),C2,A$2:A$11),0)

 

[Morfi]

Thanks. That seems to work but I have just realised that I don't want that column to include a number if there is an entry in the date of response column i.e.

Column A = list of bank holidays from row 2 to row 11
Column B = date of receipt of correspondence
Column C = deadline for response
Column D = date of response

If there is an entry in column D then I would like the formula to enter a blank for the number of days until the deadline date. Can this be done?

Thanks

 

[barry houdini]

Try like this

=IF(AND(ISNUMBER(C2),D2=""),NETWORKDAYS(TODAY(),C2,A$2:A$11),"")

 

[Morfi]

Thanks for that. It seems to be counting the number of days between the deadline date and the date of response i.e. the difference in days between column C and D. Is there a way this can show a blank rather than a figure?