Calculate and highlight 90/60/30 days from or before today in Excel
The tutorial demonstrates how to find a date any number of days from or before today, counting either all days or only business days.
Do you need to calculate the expiration date exactly 90 days from now? Or perhaps you’re wondering what date is 45 days after today? Maybe you need to determine the date that was 60 days before today, considering both business and calendar days?
Table of Contents
Date Calculator in Excel Online
Looking for a quick solution to questions like “What is 90 days from today?” or “What is 60 days before today?” Just type the number of days in the corresponding cell, press Enter, and you’ll get your answer right away
How to calculate 30/60/90 days from today in Excel
To find a date N days from today, use the TODAY function to get the current date and then add the number of days you want to it.
To get a date that occurs exactly 30 days from today:
=TODAY()+30
To calculate 60 days from today:
=TODAY()+60
What date is 90 days from now? I guess you already know how to get it 🙂
=TODAY()+90
To make a generic today plus N days formula, input the number of days in some cell, say B3, and add that cell to the current date:
=TODAY()+B3
Now, your users can type any number in the referenced cell and the formula will recalculate accordingly. As an example, let’s find a date that occurs 45 days from today:
How this formula works
Excel stores dates as serial numbers starting from January 1, 1900, which is represented by the number 1. So, the formula adds today’s date (as a number) and the number of days you specify. The TODAY() function is volatile and updates automatically every time you open or recalculate the worksheet, so it will always show the current date.
For example, today’s date is April 19, 2018, represented by the serial number 43209. To find a date 100 days from now, you perform the following calculation:
=TODAY() + 100
= August 03, 2024 + 100
= 45507 + 100
= 45607
Convert the serial number 45607 to the Date format, and you’ll get NOV 11, 2024, which is exactly 100 days after today.
How to get 30/60/90 days before today in Excel
To calculate N days before today, subtract the required number of days from the current date. For example:
90 days before today:
=TODAY()-90
60 days prior to today:
=TODAY()-60
45 days before today:
=TODAY()-45
Or, make a generic today minus N days formula based on a cell reference:
=TODAY()-B3
In the screenshot below, we calculate a date that occurred 30 days before today.
How to calculate N business after/prior to today
Microsoft Excel has several functions to calculate working days based on a start date or between any two dates you specify.
In the examples below, we’ll use the WORKDAY function, which returns a date that is a certain number of working days before or after the start date, excluding weekends (Saturday and Sunday). If your weekends are different, use the WORKDAY.INTL function, which lets you customize the weekend days.
To find a date N business days from today, use this formula:
WORKDAY(TODAY(), N days)
Here are a few examples:
10 business days from today
=WORKDAY(TODAY(), 10)
30 working days from now
=WORKDAY(TODAY(), 30)
5 business days from today
=WORKDAY(TODAY(), 5)
To get a date N business days before today, use this formula:
WORKDAY(TODAY(), –N days)
And here are a couple of real-life formulas:
90 business days prior to today
=WORKDAY(TODAY(), -90)
15 working days before today
=WORKDAY(TODAY(), -15)
To make your formula more flexible, replace the hardcoded number of days with a cell reference, say B3:
N business days from today:
=WORKDAY(TODAY(), B3)
N business days before today:
=WORKDAY(TODAY(), -B3)
In a similar manner, you can add or subtract weekdays to/from a given date, and your Excel date calculator can look like this.
How to highlight dates 30, 60 and 90 days from today
When calculating expiration or due dates, you might want to make the results more visual by color-coding the dates based on the number of days left before expiration. You can do this with Excel’s Conditional Formatting.
For example, let’s create 4 conditional formatting rules using these formulas:
- Green: more than 90 days from now
=C2>TODAY()+90 - Yellow: between 60 and 90 days from today
=C2>TODAY()+60 - Amber: between 30 and 60 days from today
=C2>TODAY()+30 - Red: less than 30 days from now
=C2<TODAY()+30
Where C2 is the topmost expiry date.
Here are the steps to create a formula-based rule:
- Select all the cells with the expiry dates (B2:B10 in this example).
- On the Home tab, in the Styles group, click Conditional Formatting > New Rule…
- In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
- In the Format values where this formula is true box, enter your formula.
- Click Format…, switch to the Fill tab and select the desired color.
- Click OK two times to close both windows.
For the color codes to apply correctly, the rules should be sorted exactly in this order: green, yellow, amber, red:
If you don’t want to bother about the rules order, use the following formulas that define each condition exactly, and arrange the rules as you please:
Green: over 90 days from now:
=C2>TODAY()+90
Yellow: between 60 and 90 days from today:
=AND(C2>=TODAY()+60, C2<=TODAY()+90)
Amber: between 30 and 60 days from today:
=AND(C2>=TODAY()+30, C2<TODAY()+60)
Â
Red: less than 30 days from today:
=C2<TODAY()+30
To include or exclude the boundary values from a certain rule, use the less than (<), less than or equal to (<=), greater than (>), greater than or equal to (<=) operators as you see fit.
In a similar manner, you can highlight past dates that occurred 30, 60 or 90 days ago from today.
- Red: more than 90 days before today:
=B2<TODAY()-90 - Amber: between 90 and 60 days before today:
=AND(B2>=TODAY()-90, B2<=TODAY()-60) - Yellow: between 60 and 30 days before today:
=AND(B2>TODAY()-60, B2<=TODAY()-30) - Green: less than 30 days before today:
=B2>TODAY()-30
That’s how you calculate dates that are 90, 60, 30, or any number of days from or before today in Excel. To see the formulas and conditional formatting rules discussed in this tutorial, you can download our sample workbook below
