# Calculate Number of Days Overdue using either column, with one taking precedence over the other.

#### jianh

##### New Member
Hello, my 2nd post here and I'm pulling hair on another stuck Excel sheet. I've got an Excel sheet prepared however just couldn't upload it here.

Actually just one question however with several conditions so I'm putting them into bullet points numbered below:

1.
For each document, how do I calculate and display on column F how many days are overdue 10 days after using either "Revision Date" or "Actual Submission Date"?
For eg, M-1700 is received on 20/07/2021, so column F will only start calculating days overdue from 30/07/2021 onwards (10 days after), i.e. on 01/08/2021,
Column F will display "2" for M-1700 to say this document is 2 days overdue.

2.
I would like "Actual Submission Date" to take precedence, however only if "Actual Submission Date" is empty, use "Revision Date" to calculate.

3.
If both dates on a particular document are empty, this means this document has not been submitted yet and shall only show "N/A" on Column F, and no calculation is required.

4.
If a particular document has actual return date, regardless of "Revision Date" or "Actual Submission Date", column F shall only show "N/A", and no calculation is required.

5.
Date format shown is DD/MM/YYYY.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### jianh

##### New Member
For Document G-1200, i used:

=IF(OR(E3<>"",C3=""),"N/A",NETWORKDAYS.INTL(D3+10,TODAY(),"0000011"))

and Excel returns "31727" which is incorrect, because on this document "Actual Submission Date" is empty.

#### Gokhan Aycan

##### Active Member
Excel Formula:
``=If (E3 <> "" , "N/A" , If (D3 <> "" , calc_for_Actual , If (C3 <> "" , calc_for_Revision , "N/A")))``

should do it I suppose.

#### jianh

##### New Member
Excel Formula:
``=If (E3 <> "" , "N/A" , If (D3 <> "" , calc_for_Actual , If (C3 <> "" , calc_for_Revision , "N/A")))``

should do it I suppose.
hey thanks for the quick reply. I tested on my spreadsheet however it returned with either "FALSE" or "#VALUE" and shows no number so some fixing is still required.

I did come up with:
=IF(OR(E3<>"",C3="",D3=""),"N/A",IF(D3<>"",NETWORKDAYS.INTL(D3+10,TODAY(),"0000011"),NETWORKDAYS.INTL(C3+10,TODAY(),"0000011")))
Some numbers appear, no "FALSE" or "#VALUE". However, as I trial with plugging in dates for either C3 or D3, Column F still shows "N/A" so still stuck at it.

Just need to point out that, Saturday and Sunday are excluded, hence "000011". I forgot to mention this in my original post.

#### jianh

##### New Member
I tried on:
=IFS(
E3<>"","N/A",
AND(C3="",D3=""),"N/A",
D3<>"",NETWORKDAYS.INTL(I4+10,TODAY(),"0000011"),"N/A",
C3<>"","N/A",NETWORKDAYS.INTL(F4+10,TODAY(),"0000011"))
D3 seems to work well either with date or empty.

C3 seems to work well if it's empty but if C3 has a date, Column F will now show #VALUE.

E3 works well.

#### jianh

##### New Member
! SOLVED !

This works:
=IFS(
E3<>"","N/A",
AND(C3="",D3=""),"N/A",
D3<>"",NETWORKDAYS.INTL(I4+10,TODAY(),"0000011"),
C3<>"",NETWORKDAYS.INTL(F4+10,TODAY(),"0000011"))

Thanks.

#### Gokhan Aycan

##### Active Member
If the following is correct, the formula I posted also works. It was the date formatting that caused the issue. My system locale is set for MM/DD/YYYY:

Excel Formula:
``=IF(E2<>"","N/A",IF(D2<>"",NETWORKDAYS.INTL(D2+10,TODAY(),"0000011"),IF(C2<>"",NETWORKDAYS.INTL(C2+10,TODAY(),"0000011"),"N/A")))``

Replies
7
Views
553
Replies
3
Views
761
Replies
1
Views
359
Replies
2
Views
60
Replies
1
Views
1K

1,186,112
Messages
5,955,905
Members
438,225
Latest member
rsur

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back