If you are working with dates and times in Google Sheets, you may need to calculate the difference between two dates in weeks and days. There is no one-function solution here, so we need to be creative. Fortunately, Google Sheets provides several functions for us to use.
Contents
Example – Weeks and Days
Here’s how you can find the number of weeks and days between two times in Google Sheets:
- Enter the two dates you want to compare in separate cells. For example, to find the difference between January 1st, 2023, and May 10th, 2023, enter those dates in cells
A1
andB1
, respectively. - To convert the difference to weeks and days, enter the following formula in another cell:
=INT((B1-A1)/7) & " weeks, " & MOD(B1-A1,7) & " days"
.- First, the formula subtracts the earlier date in
A1
from the later date inB1
. - The formula divides the difference by
7
to get the number of weeks. - Then uses the INT function to round down the result to the nearest whole number.
- It then uses the MOD function to get the remainder, which represents the number of days left over after dividing by
7
. - Last, you join the parts of the formula with
&
s to concatenate them into a text string.
- First, the formula subtracts the earlier date in
- The result shows the number of weeks and days between the two dates in the format “X weeks, Y days”.
For example, if you apply this formula to January 1st, 2023
, and May 10th, 2023
, you’ll get the result 18 weeks, 3 days.
Live Example in Sheets
Feel free to use this Google Sheet with the examples from this article.
TIMEDIF Add-On – No Formulas
For a mixed output with years, weeks, or months, consider using the TIMEDIF add-on. This add-on creates an accurate result with no code.
Simply highlight the two times and press the “Calculate active range” button.
Conclusion
In conclusion, calculating the number of weeks and days between two times in Google Sheets is possible, thanks to the various built-in functions available, and simple if you’re using the TIMEDIF add-on.