# 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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### 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

ADVERTISEMENT

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.

Any help please? Thanks.

#### 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")))``

Anyway, glad it worked.

Replies
7
Views
89
Replies
1
Views
44
Replies
0
Views
165
Replies
2
Views
324
Replies
0
Views
87

Threads
1,148,172
Messages
5,745,176
Members
423,931
Latest member
thangvan114

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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