Copyright Notice
The content in this file was created by Mynda Treacy from My Online Training Hub.
Individual users are permitted to recreate the examples for personal practice only.
Recreating the examples for training or demonstration to others is not permitted, unless writt
The workbook and any sheets within must be accompanied by the following copyright notice:
This sheet must remain in any file that uses this data and or these techniques.
Any uses of this workbook and/or data must include the above attribution.
mitted, unless written consent is granted by Mynda Treacy.
g copyright notice: My Online Training Hub ©.
IF Formulas read tutorial watch tutorial
Loan items >= 90 days old are due to be returned.
Item Loan Date Status IF Formula
DA485 1/1/2021 Due =IF(TODAY()-Table1[[#This Row],[Loan Date]]>=90
BP717 2/28/2021 Due
AR329 3/19/2021 Due
SE951 4/25/2021 Due
DA523 6/1/2021 Due
ch tutorial
his Row],[Loan Date]]>=90,"Due","")
Nested IF Formulas read tutorial watch tutorial
Loan items > 90 days old are overdue, items = 90 days old are due and items < 90 days old a
Item Loan Date Status Nested IF Formula
DA485 1/1/2021 Overdue =IF(TODAY()-Table2[[#This Row],[Loan Date]]>90,
BP717 2/28/2021 Overdue
AR329 3/19/2021 Overdue
SE951 4/25/2021 Overdue
DA523 6/1/2021 Overdue
ch tutorial
and items < 90 days old are not due.
his Row],[Loan Date]]>90,"Overdue",IF(TODAY()-Table2[[#This Row],[Loan Date]]=90,"Due","Not Due"))
,"Due","Not Due"))
IFS Function read tutorial watch tutorial
Available in Excel 2019 onward or with a Microsoft 365 license.
Loan items > 90 days old are overdue, items = 90 days old are due and items < 90 days old a
Item Loan Date Status IFS Formula
DA485 1/1/2021 Overdue {=IFS(TODAY()-Table3[[#This Row],[Loan Date]
BP717 2/28/2021 Overdue
AR329 3/19/2021 Overdue
SE951 4/25/2021 Overdue
DA523 6/1/2021 Overdue
watch tutorial
items < 90 days old are not due.
This Row],[Loan Date]]>90,"Overdue",TODAY()-Table3[[#This Row],[Loan Date]]=90,"Due",TRUE(),"Not Due
e",TRUE(),"Not Due")}
Alternative to too many nested IFs
read tutorial
The most common mistake is nesting too many times. More than 3 nests probably calls for a VL
VLOOKUP
Item Loan Date Days Old Status Formula
DA485 1/1/2021 1510 Overdue =VLOOKUP(TODAY()-Table6[[#This
BP717 2/28/2021 1452 Overdue
AR329 3/19/2021 1433 Overdue
SE951 4/25/2021 1396 Overdue
DA523 6/1/2021 1359 Overdue
XLOOKUP
Item Loan Date Days Old Status Formula
DA485 1/1/2021 1510 #NAME? =_xlfn.xlookup(TODAY()-Table4[[#T
BP717 2/28/2021 1452 #NAME?
AR329 3/19/2021 1433 #NAME?
SE951 4/25/2021 1396 #NAME?
DA523 6/1/2021 1359 #NAME?
ead tutorial watch tutorial
nests probably calls for a VLOOKUP or XLOOKUP solution.
From To Status
KUP(TODAY()-Table6[[#This Row],[Loan Date]],Table7[],3,TRUE()) 0 45 New Loan
46 89 Impending
90 90 Due
91 9999 Overdue
From To Status
lookup(TODAY()-Table4[[#This Row],[Loan Date]],Table5[From],T 0 45 New Loan
46 89 Impending
90 90 Due
91 9999 Overdue
More Resources
Tutorials
Excel Functions...............................................................
Charting Blog Posts.........................................................
Excel Dashboard Blog Posts............................................
Webinars
Excel Dashboards & Power BI.........................................
Courses
Advanced Excel...............................................................
Advanced Excel Formulas...............................................
Power Query...................................................................
PivotTable Quick Start....................................................
Xtreme PivotTables.........................................................
Power Pivot....................................................................
Excel Dashboards............................................................
Power BI.........................................................................
Excel for Decision Making Under Uncertainty................
Excel for Finance Professionals.......................................
Excel Analysis ToolPak....................................................
Excel for Customer Service Professionals.......................
Excel for Operations Management.................................
Financial Modelling.........................................................
Support
Excel Forum....................................................................
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
