Excel typically formats dates based on your region. For example, Excel users in the United States will likely be accustomed to the MM/DD/YYYY format, while users in the United Kingdom may be accustomed to the DD/MM/YYYY format. In either case, it’s not always easy to calculate the number of days between two specific dates, especially if the dates are separated by several months or years.
Fortunately, Excel makes it easy to calculate the time between dates — if you know the right formulas to use. But since calculating days between two dates isn’t something most people do on a regular basis, a lot of Excel users don’t actually know these formulas. Even if you do know how to calculate the difference in dates, you may still not know all the different ways to manage date calculation in Excel.
In today’s guide, we’re going to go over everything you need to know about finding the difference between two dates in Excel (without breaking out the calendar and calculator). And remember, if you need additional help with Excel, you can always reach out to the trained IT experts at Geeker. Now let’s get started!
How To Calculate Days Between Two Dates
The easiest way to calculate the dates in Excel is to simply subtract the cell containing the later date from the cell containing the earlier date. For example, let’s say you have the following table of dates in your spreadsheet:
| A | |
| 1 | 01/20/2020 |
| 2 | 02/21/2020 |
| 3 | 03/15/2021 |
| 4 | 04/26/2022 |
| 5 | 05/28/2023 |
Now assume that you want to find out the number of days between 01/20/2020 and 05/28/2023. Rather than pulling out the calendar, you simply need to click on an empty cell and enter the following formula:
=A5-A1
This will take the later date in A5 (05/28/2023) and subtract the earlier date in A1 (01/20/20). Once you hit enter, you’ll automatically get the number of days between the two dates. In this case, the result would be 1,224 days.
DAYS Function
While subtracting one cell from another is the quickest way to calculate the difference between two dates in number of days, it’s not the only way. In fact, there’s a specific Excel function that serves this exact purpose. To find the same difference as the previous example, you would simply need to choose a blank cell and input this formula:
=DAYS(A5, A1)
*Note: If you use either of the aforementioned methods, you always have to put the later date (or “end” date) first.
DATEDIF Function
The DATEDIF function is another common way to calculate the amount of time between two dates. However, unlike the DAYS function or subtracting cells that contain dates, DATEDIF requires you to put the earlier date (or “start” date) first. Here is what a sample DATEDIF formula might look like:
=DATEDIF(A1,A5,”D”)
The “D” unit at the end of the formula stands for “days.” In the sections below, we’ll discuss how you can adjust this formula to calculate months.
How To Calculate Weeks Between Two Dates
Unfortunately, there’s no built-in formula just for calculating the number of weeks between two dates in Excel. However, there are some easy workarounds to this issue. Unlike months, weeks always have the same number of days in them. So, as long as you can calculate the number of days between two dates, you can also calculate the number of weeks by dividing the result by 7.
For example, let’s say you use the formulas outlined previously and get a result of 1,225 days. You can simply divide that number by 7, with a result of 175 weeks. Thankfully, you still don’t have to break out the calculator to get this done. If you use the DAYS or DATEDIF functions, you can just divide the result by 7 in the formula to calculate the number of weeks, like this:
=DAYS(A5, A1)/7
=DATEDIF(A1,A5,”D”)/7
How To Calculate Months Between Two Dates
If you want to calculate the number of months between two dates, you can still use the DATEDIF function. Rather than using the days unit (D), you’ll need to use the months unit (M). Keep in mind that this will calculate the full months between the two dates. For example, if you have two dates that are 7.5 months apart, the formula will return a result of 7. Here is an example using the same dates as before:
=DATEDIF(A1,A5,”M”)
However, this may not suit your needs, particularly if you prefer to get an exact result, rather than a result that is rounded down to the nearest whole number. If you want to get a more precise result, you can use the DATEDIF function for days, and then divide the result by 30. As you can imagine, this still isn’t a perfect solution, as not all months have 30 days. However, it will give you a result that is very close to the precise number of months separating the two dates. Here is a sample formula:
=DATEDIF(A1,A5,”D”)/30
How To Calculate Age From Date Of Birth
The easiest way to calculate age based on date of birth is by using the TODAY function. All you have to do is enter the date of birth in a cell. Let’s say the DOB in question is 09/14/1985 and you enter it in the A2 cell. Then, you would need to pick an empty cell and enter the following formula:
=TODAY()-A2
Alternatively, you can switch to the YEAR and NOW functions:
=(YEAR(NOW())-YEAR(A2))
These will give you the correct age by subtracting the date of birth from today’s date. However, if you want to calculate age using a different date or you want a more precise result, you won’t be able to use either of these functions.
If you want to know the age without rounding down to the nearest whole number (i.e. age in years), you can use the YEARFRAC function. This function also makes it easy to calculate age using any date you choose, not just today’s date. For example, let’s say that you have the DOB (09/14/1985) in cell A2 and a completely different date (10/24/2022) in cell A3. To calculate the age that a person born on the first date would be by the second date, you can use the following formula:
=YEARFRAC(A2,A3)
And there you have it! Now you know how to calculate the days, weeks, and months between different dates. You even know how to calculate age in Excel! But as always, practice makes perfect, so if you have difficulty with any of these methods, be sure to double-check your formulas and ensure that all data has been inputted correctly.
Need help from dependable Excel experts? Reach out to Geeker today for on-demand IT and software solutions!