Modifying a DATE / INDEX AGGREGATE formula to return TEXT if blank cell present instead of 1900 or 0-Jan-00 dates

willow1985

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
929
Office Version
  1. 365
Platform
  1. Windows
I am looking for help with these 2 formulas:

=DATE(YEAR([@[Previous Audit Date]])+1,MONTH([@[Previous Audit Date]])-2,DAY([@[Previous Audit Date]]))
=IFERROR(@INDEX($E$2:E74,AGGREGATE(14,6,(ROW($B$2:B74)-ROW($B$2)+1)/($B$2:B108=B74),2)),"")

I know the spreadsheet is messy at the moment as many people have been playing around in it, however what these 2 formulas do is find the previous audit completion date and return the next schedule date. However these formulas don't know how to recognize if the previous audit date is blank or if that particular audit was never done before to begin with.

What I am looking for is instead of returning "October-1900" for if there is a blank cell for the previous audit for the formula to return "Previous Audit Incomplete"
If it cannot find it at all the same message is fine as well

Thank you to anyone who can help!

Company Internal Audit Schedule.xlsm
ABCDEFG
54June-2022Process Audit 6 - 1Employee 427-Aug-21
55July-2022Process Audit 6 - 1Employee 230-Sep-22Y19-Sep-21
56July-2022Process Audit 4 - Group1Employee 226-Sep-22Y30-Sep-21
57July-2022Process Audit 6 - (CA)1Employee 530-Sep-22Y30-Sep-21
58August-2022Process Audit 2 - 1Employee 429-Oct-21
59September-2022Process Audit 1 - 1Employee 428-Nov-21
60October-2022Process Audit 1 - (CA)1Employee 525-Nov-22Y15-Dec-21
61October-2022Process Audit 1 - 1Employee 230-Nov-22N21-Jul-21
62March-2023Process Audit 2 - (US)3Employee 116-May-22
63March-2023Process Audit 4 - (US)3Employee 117-May-22
64March-2023Process Audit 5 - (US)3Employee 118-May-22
65March-2023Process Audit 6 - (US)3Employee 118-May-22
66June-2023Process Audit 3 - Group1Employee 212-Aug-22
67July-2023Process Audit 4 - Group1Employee 326-Sep-22
68July-2023Process Audit 6 - 1Employee 430-Sep-22
69July-2023Process Audit 6 - (CA)1Employee 230-Sep-22
70August-2023Process Audit 2 - (CA)1Employee 625-Oct-22
71September-2023Process Audit 1 - (CA)1Employee 325-Nov-22
72October-1900Process Audit 1 - 10-Jan-00
73September-2023Process Audit 1 - 1Employee 530-Nov-22
74October-1900Process Audit 2 - 10-Jan-00
75September-2023Process Audit 2 - 1Employee 530-Nov-22
76October-1900Process Audit 6 - 10-Jan-00
Division Schedule
Cell Formulas
RangeFormula
G54,G61G54=IFERROR(@INDEX($E$2:E54,AGGREGATE(14,6,(ROW($B$2:B54)-ROW($B$2)+1)/($B$2:B54=B54),2)),"")
G55G55=IFERROR(INDEX($E$2:E55,AGGREGATE(14,6,(ROW($B$2:B55)-ROW($B$2)+1)/($B$2:B91=B55),2)),"")
G56G56=IFERROR(INDEX($E$2:E56,AGGREGATE(14,6,(ROW($B$2:B56)-ROW($B$2)+1)/($B$2:B88=B56),2)),"")
G57,G62:G76G57=IFERROR(INDEX($E$2:E57,AGGREGATE(14,6,(ROW($B$2:B57)-ROW($B$2)+1)/($B$2:B91=B57),2)),"")
A54:A60,A62:A71,A73:A76A54=DATE(YEAR([@[Previous Audit Date]])+1,MONTH([@[Previous Audit Date]])-2,DAY([@[Previous Audit Date]]))
A72A72=IFERROR(DATE(YEAR([@[Previous Audit Date]])+1,MONTH([@[Previous Audit Date]])-2,DAY([@[Previous Audit Date]])),"Previous Audit Date not found")
G59G59=IFERROR(@INDEX($E$2:E59,AGGREGATE(14,6,(ROW($B$2:B59)-ROW($B$2)+1)/($B$2:B65=B59),2)),"")
G60G60=IFERROR(INDEX($E$2:E60,AGGREGATE(14,6,(ROW($B$2:B60)-ROW($B$2)+1)/($B$2:B93=B60),2)),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:P2,A81:P4985,D62:I80,A3:I61,A62:A80,J3:P80Expression=$P2="Past Due"textNO
A2:P2,A81:P4985,D62:I80,A3:I61,A62:A80,J3:P80Expression=$L2="Y"textNO
A2:P2,A81:P4985,D62:I80,A3:I61,A62:A80,J3:P80Expression=AND($F2="N",$E2<>"")textNO
A2:P2,A81:P4985,D62:I80,A3:I61,A62:A80,J3:P80Expression=$F2="Y"textNO
Cells with Data Validation
CellAllowCriteria
B62:B1048576List='Audit List'!$I$2:$I$18



Note: Ignore A72 that was me playing around...
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
I couldn't figure out the table information you have in your sample, so I made what I could. I hope this helps.
This could also be more simplified in a Let statement.

WorkBook1.xlsx
ABCD
1Audit TypeAudit DateNext Audit
2Process Audit 1 - 9/5/20209/30/2023
3Process Audit 1 - 11/30/20229/30/2023
4Process Audit 1 - 11/25/20229/30/2023
5Process Audit 1 - 9/30/2023
6Process Audit 1 - (CA)11/25/20229/25/2023
7Process Audit 1 - (CA)9/25/2023
8Process Audit 2 - No Audit
9Process Audit 2 - No Audit
10Process Audit 2 - No Audit
11Process Audit 2 - (CA)No Audit
12Process Audit 2 - (US)No Audit
13Process Audit 3 - GroupNo Audit
14Process Audit 4 - (US)No Audit
15Process Audit 4 - Group10/15/20218/15/2022
16Process Audit 4 - Group8/15/2022
17Process Audit 5 - (US)No Audit
18Process Audit 6 - 7/30/2023
19Process Audit 6 - 9/30/20227/30/2023
20Process Audit 6 - 7/30/2023
21Process Audit 6 - 7/30/2023
22Process Audit 6 - (CA)9/30/20227/30/2023
23Process Audit 6 - (CA)7/30/2023
24Process Audit 6 - (US)No Audit
Sheet4
Cell Formulas
RangeFormula
C2:C24C2=IF(DATE(YEAR(MAXIFS($B$2:$B$100,$A$2:$A$100,$A2)),MONTH(MAXIFS($B$2:$B$100,$A$2:$A$100,$A2))+10,DAY(MAXIFS($B$2:$B$100,$A$2:$A$100,$A2)))=305,"No Audit",DATE(YEAR(MAXIFS($B$2:$B$100,$A$2:$A$100,$A2)),MONTH(MAXIFS($B$2:$B$100,$A$2:$A$100,$A2))+10,DAY(MAXIFS($B$2:$B$100,$A$2:$A$100,$A2))))
 
Upvote 0
awoohaw said:
I couldn't figure out the table information you have in your sample, so I made what I could. I hope this helps.
This could also be more simplified in a Let statement.

WorkBook1.xlsx
ABCD
1Audit TypeAudit DateNext Audit
2Process Audit 1 - 9/5/20209/30/2023
3Process Audit 1 - 11/30/20229/30/2023
4Process Audit 1 - 11/25/20229/30/2023
5Process Audit 1 - 9/30/2023
6Process Audit 1 - (CA)11/25/20229/25/2023
7Process Audit 1 - (CA)9/25/2023
8Process Audit 2 - No Audit
9Process Audit 2 - No Audit
10Process Audit 2 - No Audit
11Process Audit 2 - (CA)No Audit
12Process Audit 2 - (US)No Audit
13Process Audit 3 - GroupNo Audit
14Process Audit 4 - (US)No Audit
15Process Audit 4 - Group10/15/20218/15/2022
16Process Audit 4 - Group8/15/2022
17Process Audit 5 - (US)No Audit
18Process Audit 6 - 7/30/2023
19Process Audit 6 - 9/30/20227/30/2023
20Process Audit 6 - 7/30/2023
21Process Audit 6 - 7/30/2023
22Process Audit 6 - (CA)9/30/20227/30/2023
23Process Audit 6 - (CA)7/30/2023
24Process Audit 6 - (US)No Audit
Sheet4
Cell Formulas
RangeFormula
C2:C24C2=IF(DATE(YEAR(MAXIFS($B$2:$B$100,$A$2:$A$100,$A2)),MONTH(MAXIFS($B$2:$B$100,$A$2:$A$100,$A2))+10,DAY(MAXIFS($B$2:$B$100,$A$2:$A$100,$A2)))=305,"No Audit",DATE(YEAR(MAXIFS($B$2:$B$100,$A$2:$A$100,$A2)),MONTH(MAXIFS($B$2:$B$100,$A$2:$A$100,$A2))+10,DAY(MAXIFS($B$2:$B$100,$A$2:$A$100,$A2))))
Click to expand...
The formula you provided is filling in dates even though the completion date of the previous is blank or does not exist. The formulas I have work, but the only thing I would like to modify is, if column E is blank to return a "No previous Audit" instead of a 1900 date
 
Upvote 0
I am assumng that the value in G72, will be zero, as all the values will correspond to date values. If so, the you could try
=IFS(E72=0,"Previous Audit Not Found",True,IFERROR(DATE(YEAR([@[Previous Audit Date]])+1,MONTH([@[Previous Audit Date]])-2,DAY([@[Previous Audit Date]])),""))
 
Upvote 0
I'm not sure if I just got it wrong or if my guess as to your table information was incorrect. The sample doesn't give headers to to your columns, so I guessed. I could not just paste the worksheet you posted and start working with where your errors are. Can you post the table showing the headers please? I may still get it wrong 😵‍💫
 
Upvote 0
Try these:

Excel Formula: 
=IF([@[Previous Audit Date]]="","Previous Audit Incomplete",DATE(YEAR([@[Previous Audit Date]])+1,MONTH([@[Previous Audit Date]])-2,DAY([@[Previous Audit Date]])))

and
Excel Formula: 
=LET(returnValue,IFERROR(INDEX($E$2:E54,AGGREGATE(14,6,(ROW($B$2:B54)-ROW($B$2)+1)/($B$2:B90=B54),2)),""),
          IF(returnValue="","Date Value is Blank",returnValue))

PS: For tables the column headings are important, could you please include them the next time you use XL2BB. In this case you just hide the rows between the heading row and Row 59 before using XL2BB to copy the data.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Question Question
Create List of Cell Values with Criteria
Replies
1
Views
411
gtd526
gtd526
UpToTheGills
  • Solved
Formula to find last date of a given MONTH and return value from another cell on the same row
Replies
2
Views
798
UpToTheGills
UpToTheGills
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
710
MrDB4Excel
MrDB4Excel
G
  • Question Question
Copy Range of Cells based on Criteria, Paste it on rows below it, Do This Until All conditions are met, Then Delete All Copied Rows
Replies
0
Views
570
glaccounting
G
J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
127
Fluff
Fluff

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

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

Which adblocker are you using?

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
Back
Top