Ever found yourself needing to calculate the number of days between two dates in Google Sheets? The built-in DAYS function can help, but what if you want to display the result as "Days Since" followed by the start date? Google Sheets' TEXT and CONCAT functions can achieve this, but it's not the most straightforward process. So, let's dive into a simple and efficient formula to calculate and display the number of days since a given date.
Before we delve into the formula, let's ensure we have the basics covered. Firstly, Google Sheets automatically recognizes dates when you type them in a specific format (e.g., YYYY-MM-DD). Secondly, the DAYS function calculates the number of days between two dates. Now, let's combine these to create a custom "Days Since" formula.
Calculating Days Since a Date
The key to this formula is using textbooks and concatenation, along with a bit of DATE amazedcery. We'll start by creating two dates, then calculate the difference using DAYS. Finally, we'll format the result as "Days Since" followed by the start date.
Creating Start and End Dates
Let's assume that cell A1 contains the start date, and cell B1 contains the current date. Both should be in date format. To calculate the number of days between these two dates, use the following formula in cell C1:
DAYS(B1, A1)
If B1 is later than A1, this will give you a positive number. If B1 is earlier, you'll get a negative number.
Formatting the Result
Now, let's format the result as "Days Since" followed by the start date. In cell D1, enter the following formula:
CONCAT(TEXT(C1, "0"), " Days Since ", TEXT(A1, "long"))
Here's what's happening:
- TEXT(C1, "0"): Formats the result of the DAYS function as a whole number.
- TEXT(A1, "long"): Formats the start date as a long string (e.g., "January 1, 2022").
- CONCAT(..., "...", ...): Combines the two formatted elements with the text " Days Since ".
The final result will look something like this: "30 Days Since January 1, 2022".
Automatic Updates
To automatically update the number of days and the display as new dates are added, just copy the formulas down the columns. Google Sheets will automatically adjust the dates and recalculate the results.
Absent Dates
If there are no dates in the range, the DAYS function will return an error. To avoid this, you can wrap the formula in an IFERROR function, like this:
IFERROR(DAYS(B1, A1), "")
This will display nothing (an empty cell) if there are no dates in the range, making your sheet cleaner and more manageable.
Dynamic Formatting
To create dynamic formatting for the "Days Since" result, you can use conditional formatting. Select cell D1, click "Format" in the toolbar, then "Conditional formatting". Set the condition to "Less than" a specific number (e.g., 7), and choose a background or text color. This will highlight days that are less than one week old.
You've now learned how to create a custom "Days Since" formula in Google Sheets, Calculate the number of days, and automatically update and format your results. Happy spredsheeting!