The TODAY function returns the current date – but will update itself when you open your worksheet on another day. This is great news if you need a formula to work out whether an invoice is overdue or if you need to know the number of days left until a deadline.
Watch the video, read the tutorial and download the featured file
In our example we are working on an invoice spreadsheet where we need to first of all calculate the number of days until an invoice is due.
We can use the TODAY function to perform this calculation; =C2-today()
Initially Excel returns the answer as a date.
We need to format the cell to show a number and the copy the formula down the rest of the column.
We could also use the TODAY function to conditionally format the due dates that are overdue. To do this we need to select the dates and then on the Ribbon’s Home tab, the Styles group click on the Conditional Formatting menu.
From the menu select Highlight Cells Rules and then Less Than…
In the Less Than dialog enter =today() as your criteria and then click on OK.
Here’s what you dates will look like. The great thing about using the TODAY function is that conditional formatting is always going to be based on the current date – so it will always format the dates in the past.
Finally we could use the TODAY function in an IF statement to tell us whether the invoice is overdue or not. The formula would be =IF(C2<TODAY(),”OVERDUE”, “OK”)
Here’s how our spreadsheet now looks…
