Mastering the 90-Day Calculation in Excel: A Comprehensive Guide

Excel is an invaluable tool for various calculations, including date-related tasks. One common query is how to calculate 90 days from a given date. This comprehensive guide will walk you through multiple methods to achieve this with ease, catering to different skill levels and needs.

Method 1: Using the DATE Function

The DATE function is a straightforward way to add a specific number of days to a date. Here's how you can use it to calculate a date 90 days from now:

1. Enter the starting date in a cell (e.g., A1)
2. In another cell, enter the formula: =DATE(YEAR(A1), MONTH(A1), DAY(A1) + 90)
3. Press Enter, and you'll get the date 90 days from the start date.

📝 Note: This method ensures the resulting date has the same day of the month as the start date. If the start date is the 30th, the calculated date will also be the 30th, even if the month has fewer days.

Method 2: EDATE Function

The EDATE function is specifically designed for adding or subtracting a specified number of months from a date. While it's typically used for monthly calculations, it can also be used for 90-day calculations with a slight modification.

1. Enter the starting date in a cell (e.g., A1)
2. In another cell, enter the formula: =EDATE(A1, 3)
3. Press Enter, and you'll get the date 90 days from the start date.

📝 Note: The EDATE function calculates 90 days by adding 3 months. This is because, on average, a month has about 30 days, so 3 months equate to 90 days.

Method 3: Using the EOMONTH Function

The EOMONTH function is similar to the EDATE function but returns the last day of the month. It can be useful when you want to calculate the last day of the month 90 days from a given date.

1. Enter the starting date in a cell (e.g., A1)
2. In another cell, enter the formula: =EOMONTH(A1, 3) + 1
3. Press Enter, and you'll get the date 90 days from the start date.

📝 Note: The EOMONTH function returns the last day of the month, so adding 1 to the result gives you the date 90 days from the start date.

Method 4: Date Calculator Tools

Excel offers various date calculator tools that can simplify date calculations. These tools are especially useful for those who prefer a more visual approach.

1. Select the cell where you want the calculated date to appear.
2. Go to the Formulas tab and click on Date & Time in the Function Library group.
3. Choose the appropriate date calculator function, such as EDATE or EOMONTH, and enter the required parameters.
4. Click OK, and Excel will display the calculated date.

Method 5: Custom Function for 90-Day Calculation

If you frequently need to calculate dates 90 days in the future or past, you can create a custom function in Excel VBA. This function can be used just like any other built-in Excel function.

Creating the Custom Function

1. Open the Visual Basic Editor by pressing Alt + F11 or going to Developer > Visual Basic.
2. In the VBA Editor, go to Insert > Module to insert a new module.
3. Paste the following code into the module:

   Function Calc90Days(StartDate As Date) As Date
     Calc90Days = DateAdd("d", 90, StartDate)
   End Function
   

4. Close the VBA Editor and return to Excel.

Using the Custom Function

1. Enter the starting date in a cell (e.g., A1)
2. In another cell, enter the formula: =Calc90Days(A1)
3. Press Enter, and you'll get the date 90 days from the start date.

📝 Note: This custom function uses the DateAdd function to add 90 days to the start date.

Method 6: 90-Day Calculation with Conditional Formatting

Conditional formatting can be a useful tool for highlighting dates that are 90 days from a given date. This can be especially helpful when working with a list of dates.

1. Select the range of cells containing the dates.
2. Go to the Home tab and click on Conditional Formatting in the Styles group.
3. Choose New Rule and select Use a formula to determine which cells to format.
4. In the formula box, enter: =EDATE(A1, 3) = TODAY() (assuming the start date is in column A)
5. Click Format and choose the formatting options you want, such as font color or cell fill.
6. Click OK, and Excel will highlight the dates that are 90 days from the start date.

Advanced Tips for Date Calculations

Handling Non-Standard Date Formats

If your dates are in a non-standard format, such as mm/dd/yyyy or dd-mm-yyyy, you'll need to use the TEXT function to convert them to a format Excel can recognize.

1. Enter the formula: =DATE(VALUE(RIGHT(A1,4)), VALUE(MID(A1,4,2)), VALUE(LEFT(A1,2))) to convert a date in mm/dd/yyyy format.
2. For dd-mm-yyyy format, use: =DATE(VALUE(RIGHT(A1,4)), VALUE(MID(A1,3,2)), VALUE(LEFT(A1,2)))

Working with Date Serial Numbers

Excel internally represents dates as serial numbers. If you have dates stored as serial numbers, you can directly use them in your calculations.

1. Enter the formula: =DATE(YEAR(A1), MONTH(A1), DAY(A1) + 90) to calculate 90 days from a date serial number.

Using the DAYS Function for Date Differences

The DAYS function calculates the number of days between two dates. If you need to find the number of days between a given date and a date 90 days from it, you can use the following formula:

1. Enter the formula: =DAYS(A1, EDATE(A1, 3)) to calculate the number of days between the start date and the date 90 days from it.

Visualizing 90-Day Calculations with Charts

Excel charts can be a powerful tool to visualize date-based data. Here's how you can create a chart to visualize dates 90 days from a given date range.

1. Enter your date range in a column (e.g., A1:A10)
2. Calculate the dates 90 days from each date using one of the methods above (e.g., in column B)
3. Select the data you want to chart (e.g., A1:B10)
4. Go to the Insert tab and choose the type of chart you want (e.g., a line chart or a column chart)
5. Excel will create a chart showing the original dates and the dates 90 days from each.

Troubleshooting Common Issues

Error: "#VALUE!" or "#NUM!" in Date Calculations

If you encounter the #VALUE! or #NUM! error in your date calculations, it's likely due to an invalid date format or a date that's too far in the future or past for Excel to handle.

• Check your date format and ensure it's recognized by Excel.
• If you're calculating dates far in the future or past, consider using the DATEVALUE function to convert the date to a serial number.

Error: "Name is not defined" with Custom Functions

If you receive a "Name is not defined" error when using a custom function, it's likely because the function hasn't been defined in the current workbook. To fix this:

• Ensure the workbook containing the custom function is open.
• If the workbook is open, check that the function is spelled correctly in the formula.

Conclusion

Excel offers a range of methods to calculate dates 90 days from a given date, catering to various skill levels and needs. Whether you're a beginner or an advanced user, there's a method that suits your requirements. By mastering these techniques, you can streamline your date-related tasks and make the most of Excel's powerful features.

What is the fastest way to calculate 90 days in Excel?

+

The fastest way is to use the EDATE function, which adds or subtracts a specified number of months from a date. Simply enter the formula =EDATE(start_date, 3) to get the date 90 days from the start date.

Can I calculate 90 days from a date in the past?

+

Yes, you can. All the methods mentioned in this guide work for calculating dates in the past as well as in the future. Simply provide the past date as the start date in the formula.

How can I format the calculated date to a specific format?

+

After calculating the date, you can format it to your desired format using the Format Cells feature. Select the cell with the calculated date, right-click, and choose Format Cells. In the Number tab, select the desired date format from the Category list.

Can I use these methods to calculate a date 90 days in the past?

+

Absolutely! All the methods in this guide can be used to calculate dates in the past as well. Simply provide the past date as the start date in the formula.

What if I want to calculate a date more than 90 days in the future or past?

+

You can easily modify the formulas to calculate dates further in the future or past. For example, to calculate a date 180 days from now, simply change the number in the formula to 180 (e.g., =EDATE(start_date, 6) for 180 days using the EDATE function).