Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel ROWS Function
  • How to use Excel INDIRECT Function
  • How to get address of first cell in range in Excel
  • Perform case-sensitive Lookup in Excel
  • How to get first row number in range in Excel

Data Analysis Examples

  • How to sum a total in multiple Excel tables
  • Example of COUNTIFS with variable table column in Excel
  • How To Create Frequency Distribution in Excel
  • How to calculate average last N values in a table in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS

Data Validation Examples

  • Data validation must not exist in list
  • Excel Data validation must begin with
  • Excel Data validation whole percentage only
  • Prevent invalid data entering in specific cells
  • How To Create Drop-down List in Excel

Invoice status with nested if in Excel

by

This tutorial shows how to calculate Invoice status with nested if in Excel using the example below;

Formula

=IF(balance=0,"Paid",IF(current_date<due_date,"Open","Overdue"))

Explanation

To determine invoice status (i.e. paid, open, overdue), you can use a nested IF formula and the TODAY function.

In the example shown, the formula in G5 is:

=IF(F5=0,"Paid",IF(TODAY()<C5,"Open","Overdue"))

How this formula works

Note: the “current date” (i.e. today) for this example is May 31, 2016

Worked Example:   If this AND that OR that in Excel

With nested IF statements, the flow is from outer IF statements to inner IF statements, and the challenge is always to construct the flow so that the formula returns a logically correct result.

Worked Example:   IF, AND, OR and NOT Functions Examples in Excel

Here, the outermost IF tests first to see if the balance is zero:

=IF(F5=0

If TRUE, the formula returns “Paid”.

If not, the result of the first IF is FALSE, and another IF statement is run. This one checks to see if TODAY() is less than the due date in column C:

IF(TODAY()<C5

If TRUE, the formula returns “Open”.

Worked Example:   Generate series of dates by weekends in Excel

If FALSE, the formula returns “Overdue”.

Post navigation

Previous Post:

IF with boolean logic in Excel

Next Post:

Nested IF function example in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to get top level domain (TLD) in Excel
  • How to count number of characters of text in a cell in Excel
  • Extract last name from full name — Manipulating NAMES in Excel
  • How to use double quotes inside a formula in Excel
  • Normalize text by removing punctuations, extra spaces and more in Excel
  • How to calculate project start date based on end date in Excel
  • How to join date and text together in Excel
  • Assign points based on late time in Excel
  • Find Last Day of the Month in Excel
  • EDATE function: Description, Usage, Syntax, Examples and Explanation
  • DB function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate principal for given period in Excel
  • How to calculate compound interest in Excel
  • YIELDDISC function: Description, Usage, Syntax, Examples and Explanation
  • NPER function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles