Date Difference Formula

Hi

I have a date subtraction problem to solve.

Two columns may contain dates, in the third column I want to calculate the date difference.

First column will contain the date something was submitted for approval.

Second column will contain the date of approval.

If neither date is available the 3rd column should show no result.

If both dates are available the 3rd column should show the date difference, in net working days.

If only 1st date available 3rd column should show difference in days between that date and todays date, result in networking days.

If only 2nd date is available 3rd column should show no result or ideally an error message as approval is not actually possible without submission.

In summary:

Column 1 Column 2 Column 3
Blank Blank Blank

Date Date Networkdaysdifference

Date Blank Networkdays between date and todays date

Blank Date Blank or Error message.

So far I have created a formula to test for each of the cases individually but can’t find a way to combine both of these.

The formulae I have for column 3 are:

=IF(AND(ISNUMBER(A2),ISBLANK(B2)),NETWORKDAYS(A2,TODAY())," ")

or

=IF(AND(ISNUMBER(A2),ISNUMBER(B2)),NETWORKDAYS(A2,B2)," ")

If anyone can resolve this I’d be very grateful.

Best regards.

Henry Thompson.
Resource Planner.

Try:

Please Login or Register  to view this content.