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

[jianh]

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.

 

[jianh]

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]

=If (E3 <> "" , "N/A" , If (D3 <> "" , calc_for_Actual , If (C3 <> "" , calc_for_Revision , "N/A")))

should do it I suppose.

 

[jianh]

=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]

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]

! 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]

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:

=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.