FILTER Function

[jianh]

Hi, currently struggling with counting number of days based on dates taking data from a FILTER generated table:

This is the Root Data tab:

This Root tab is the root data tab showing vendor documents and their Revision Dates, Planned Submission Dates as well as Actual Submission Dates.

And this is Report tab:

Now what I need to do is to calculate and display on both cell G4 and H4, on these criteria:
1. On G4 count and display the number of days 10 working days after either Revision Date or Actual Submission Date, with Actual Submission Date taking precedence. This means only if Actual Submission Date is empty, then count using Revision Date. And subsequently on G5, G6, G7 and so on.

2. On H4 count and display the number of days between Planned Submission Date and today's date, and leave cell empty if Planned Submission Date is empty. And H5, H6, H7 and so on.

3. If both Revision Dates and Actual Submission Dates are empty, this document has not been received by vendor. Then both G4 and H4 should display "N/A".

In Report tab, I have utilised FILTER function to only display vendor packages documents based on dropdown list on cell J3. And this is based on range A4:F17, including A4:A17 in Root tab.

On F4 count in Report tab, my formula is:

=IFS(
OR(AND(D4="",F4=""),C4<>"Under Review"),"N/A",
F4<>"",NETWORKDAYS.INTL(J4+10,TODAY(),"0000011"),
D4<>"",NETWORKDAYS.INTL(F4+10,TODAY(),"0000011"))

On G4 count, my formula is:

=IFS(
C4="","",
OR(D4<>"",F4<>""),"N/A",
AND(D4="",F4=""),NETWORKDAYS.INTL(E4,TODAY(),"0000011"))

But right now everything is showing "N/A" no matter the circumstance. Unsure where I've done wrongly. I suspect it's the FILTER but seeking some help here while pulling my hair out again.

Also, Report tab C5 shows 0 instead of being empty, how do I let it be empty instead of showing 0?

Thank you so much.

Oh I must apologise for not being able to upload the mini sheet, using work laptop I can't install the browser plug-in. Cheers.

 

[jasonb75]

The first formula looks like it should work fine, although some of the cell references look a bit wild (J4 is used in the formula but is outside of the data range in the sheet).

Perhaps it should be

=IF(OR(AND(D4="",F4=""),C4<>"Under Review"),"N/A",IF(F4<>"",NETWORKDAYS.INTL(J4+10,TODAY(),"0000011"),NETWORKDAYS.INTL(F4+10,TODAY(),"0000011")))

For the second one, it looks like you made a mess of your <>"" and ="" testing, see if this works there.

=IF(C4="","",IF(AND(D4="",F4=""),"N/A",NETWORKDAYS.INTL(E4,TODAY(),"0000011")))

Also, Report tab C5 shows 0 instead of being empty, how do I let it be empty instead of showing 0?

You could try using conditional formatting to hide the 0 values, it would not be possible to do it as part of the filter formula with the data that you are working with.

 

[Fluff]

If you have the LET function, you can use this to get rid of the 0s

=LET(f,FILTER(Root!A1:F17,(Root!A1:A17=J3)+(J3="")),IF(ISBLANK(f),"",f))

 

[jianh]

The first formula looks like it should work fine, although some of the cell references look a bit wild (J4 is used in the formula but is outside of the data range in the sheet).

Perhaps it should be

=IF(OR(AND(D4="",F4=""),C4<>"Under Review"),"N/A",IF(F4<>"",NETWORKDAYS.INTL(J4+10,TODAY(),"0000011"),NETWORKDAYS.INTL(F4+10,TODAY(),"0000011")))

For the second one, it looks like you made a mess of your <>"" and ="" testing, see if this works there.

=IF(C4="","",IF(AND(D4="",F4=""),"N/A",NETWORKDAYS.INTL(E4,TODAY(),"0000011")))

You could try using conditional formatting to hide the 0 values, it would not be possible to do it as part of the filter formula with the data that you are working with.

Oh that is a mistake. That J4 in the formula should instead be F4. Apologies.
Thanks for the reply i'll give it a try, I appreciate it.

 

[jianh]

If you have the LET function, you can use this to get rid of the 0s

=LET(f,FILTER(Root!A1:F17,(Root!A1:A17=J3)+(J3="")),IF(ISBLANK(f),"",f))

Thanks I'll give it a try in a bit see if it works or not.

 

[Fluff]

Ok

 

[jianh]

The first formula looks like it should work fine, although some of the cell references look a bit wild (J4 is used in the formula but is outside of the data range in the sheet).

Perhaps it should be

=IF(OR(AND(D4="",F4=""),C4<>"Under Review"),"N/A",IF(F4<>"",NETWORKDAYS.INTL(J4+10,TODAY(),"0000011"),NETWORKDAYS.INTL(F4+10,TODAY(),"0000011")))

For the second one, it looks like you made a mess of your <>"" and ="" testing, see if this works there.

=IF(C4="","",IF(AND(D4="",F4=""),"N/A",NETWORKDAYS.INTL(E4,TODAY(),"0000011")))

You could try using conditional formatting to hide the 0 values, it would not be possible to do it as part of the filter formula with the data that you are working with.

Hi I evaluated my own formula and yours as well.

On the first formula:
Both mine and yours count the number of days only if Actual Submission Date cell (F4) has a date. Cell shows random 5-digit number (31799 in the case of vendor documents J-53001-05 and L-55004-01). Unsure why myself.

Other circumstances are fine they show the correct number of working days after I manually calculated on a physical table calendar just to double check.

On the second formula:
I would actually like OR(D4<>"",F4<>""),"N/A" to remain as there are documents that vendors have submitted and their submission dates will be recorded, where the circumstances within are there'll always be Revision Dates (D4), but some Actual Submission Dates (F4) are empty. So I structured it in such a way that if either cells D4 or D4 aren't empty, just show "N/A" and don't calculate number of days overdue on submission because vendor has submitted this document.

On the contrary, if none of the dates on D4 or F4 are available, then calculate the number of days overdue since between Planned Submission Date (cell E4) and today's date to show how many working days are overdue.

Now when Root tabs show empty cells on dates, the FILTER shows 00-Jan-1900 on Report tab. No idea how I could remove this. Only way I could figure out is to uncheck "Show zero in cells that have zero values" in Option menu within Excel.

 

[jasonb75]

Now when Root tabs show empty cells on dates, the FILTER shows 00-Jan-1900 on Report tab. No idea how I could remove this. Only way I could figure out is to uncheck "Show zero in cells that have zero values" in Option menu within Excel.

Have you tried using @Fluff's suggestion for that?

For the first formula problem, I can't do anything without seeing the report data that it is associated with.

For the second, I had misread some of the criteria. I'll have another look at it a bit later but think your original should work.