3 Invoice Tracker
Explication de la t âche .
On this file we have an invoice tracker for a construction business! We only do large invoices and less than 30 a year. Keeping track of them is extremely critical and knowing if they have been paid or not is a key factor.
We've chosen Excel to keep track of everything in a ledger style format. We have some invoices input in the table but we would like to get an automatic status collumn that would let us know the status of each invoice!
Here is each Status and how it should be defined:
PAID - If there is a paid date than the invoice is considered PAID
INVOICED - If there is an invoice date which is no later 45 DAYS from today it is considered INVOICED
LATE & UNPAID - If the invoice is more than 45 days old and has no paid date it should be marked as LATE
🌶 Défi épicé . 🌶
As an EXTRA CHALLENGE, it would be nice if you could make the PAID invoices line go green and the LATE invoice lines RED!
Comment faire.
This one is just one large function built around a VLOOKUP to check if it's late. There are a lot of other ways to make it work but I'll drop the function right here!
=IF(G2<>"","PAID",IF(VLOOKUP(B2,DateCheckTable,2)="Late","LATE & UNPAID","INVOICED"))
To understand how this got done and works we will need to build certain things and try to approach this formula one step at a time.
The first thing we will try and get is "PAID" to show up when you have a date in the PAID DATE collumn. To do this we need to check if there is something. To do that we will do a test which is quite useful. If the cell is different from empty, written in Excel terms it looks like this A1<>""
A1 = Cell to be tested
<> = Different from
"" = Empty (since you use " to start string and " to end string)
By typing this formula you will notice the PAID'S show up and the UNPAID SHOW UP
=IF(G2<>"","PAID","UNPAID")
Cool!
So next is understanding the VLOOKUP!
As we did in our last VLOOKUP exercice. We used an approximative match VLOOKUP and made a table starting with 0 and going to higher values. Since dates are also composed from smallest number for eldest value to highest number for more recent or future value. So a 1 is 1/1/1900 and today would be in 40000s. You need to build a table and name it. This table can look a little weird but simply it looks like this. Note that this needs to be real functions for it to properly work!
Once you input this info in the Table you will need to go and name that table : DateCheckTable
Select all the table, go in the top left name box and change the name to DateCheckTable
Once this is done you will be able to go in a new collumn and try out your Vlookup! Type out
=VLOOKUP(B2,DateCheckTable,2)
You will notice a bunch of Late's and a bunch of Still on time!
Wow awesome, if you did this properly you should have 2 collumns one that shows the paids and the other ones the still on time.
You can combine both these formulas by running an IF inside of the UNPAID section of the IF to check if they are late or not! And if they aren't get the right labels!
=IF(G2<>"","PAID",IF(VLOOKUP(B2,DateCheckTable,2)="Late","LATE & UNPAID","INVOICED"))
Here's it read in plain english - IF cell g2 is not empty, put "PAID" in the cell or else run a vlookup to check if it's late or not. And if it's late, put "LATE & UNPAID" or else it has to be "INVOICED".
It works!
When it comes to the 🌶Spicy Challenge🌶 That's a whole other ballgame.
To do this you will need to create a formula in the Conditionnal formats of your entire table!
Here are the steps and a handy GIF to help you out!
SELECT CELL A1
HIT CTRL - A
GO TO CREATE CONDITIONNAL FORMAT AND CHOOSE FORMULA
TYPE =$H1="PAID"
SET THE COLOR OF THE FONT TO GREEN
Do it again and change "PAID" by "LATE & UNPAID" and set the color of the font to RED
If you would rather see it:
