Mr A.

asked • 05/15/24

Calculating averages of dates on Excel 97

Calculate the average of the following dates using Excel (97 in my case): 3/30/2023, 5/1/2024, 12/7/2024


Answer (from Excel 97): 3/2/2024


I would like to know how Excel is coming up with this answer. Initially, I thought that if I simply add up the dates and divide by 3, then that would be the answer:


3/30/2023 + 5/1/2024 + 12/7/2024 = 20/38/6071

(20/38/6071) divided by 3 = 6.6/12.6/2023.6.


Then I thought that I could add the total number of days in between and divide that by 3. According to https://www.timeanddate.com/date/duration.html, the number of days in between 3/30/2023–5/1/2024 = 398 days and between 5/1/2024–12/7/2024 = 220 days. Thus, the total of 618 days divided by the number of 3 dates (618/3) is = 206 days.


Also, the number of days in between 3/30/2023–3/2/2024 = 338 days and in between 3/2/2024–12/7/2024 = 280 days—not that it should matter.


So how is Excel calculating this?

3 Answers By Expert Tutors

By:

Kyle L. answered • 08/03/25

Tutor
5 (2)

7+ Years of Online Tutoring Experience
About this tutor ›
About this tutor ›

Hello, thank you for taking the time to post your question!


Excel does this type of calculation based on the way that Excel stores dates as numerical numbers … so like March 30, 2023 is stored as being “45014”, which is the number of days from 1/1/1900 to 3/30/2023. Similarly May 1, 2024 is stored as “45411” and December 7, 2024 is stored as “45610” … so the average of those 3 values ends up being 45345, which is March 2, 2024.


Hopefully that gets you moving in the right direction! Feel free to reach out with a message if you have any questions beyond that or want to go over a specific example in a lesson! :)

Upvote • 0 Downvote
More
Report

Dior B. answered • 05/16/24

Tutor
4.9 (8)

Experienced Math Tutor with Specialized Experience in Elementary Math
About this tutor ›
About this tutor ›

I am using a newer version of excel but I believe the same method was used.


Instead of adding them and dividing yourself, allow excel to do the work for you:



I listed the dates out in cells A1:A3. To get the average, simply use the AVG function like so:


=AVG(A1:A3)


Hope this helps!

Upvote • 0 Downvote
More
Report

Mr A.

Thank you for responding.
Report

05/17/24

James S. answered • 05/16/24

Tutor
4.5 (2)

BEST HELP MATH TUTOR
About this tutor ›
About this tutor ›

Excel uses a base date and counts the days from that date. For some versions of Excel, I believe that date is January 1, 1900.


My rough calculations would make today 45,529 days after 1-1-1900. To get an average of three dates, Excel just adds these three date counts, divides them by 3 , and gives you the result after converting that number back to a date.


If you would like to define a date average some other way, that just requires an appropriate formula. How were you expecting the date average to be computed?



Upvote • 0 Downvote
More
Report

Mr A.

Thank you. You've been a big help. It makes sense now.
Report

05/17/24

Mr A.

Now that we've gotten that cleared up, can you explain to me what was wrong with the logic of my previous solutions: 1.) "3/30/2023 + 5/1/2024 + 12/7/2024 = 20/38/6071 (20/38/6071) divided by 3 = 6.6/12.6/2023.6." 2.) "Thus, the total of 618 days divided by the number of 3 dates (618/3) is = 206 days." (I would've thought that at least this would've been right.)
Report

05/17/24

James S.

tutor
The years, days, and months are not kept separately. There is one number, the number of days from a fixed date. I will try to give you a complete example later today.
Report

05/18/24

James S.

tutor
Day 0 on the version of Excel I am using is 12/30/1899. 3/30/2023 has an underlying value of 45,015. You can see this by changing the format to Number-General. 5/1/2024 has an underlying value of 45,413. And 12/7/2024 has an underlying value of 45,633.
Report

05/18/24

James S.

tutor
When you take the average of these dates, you are adding these three numbers, dividing by 3, and displaying that number as a date.
Report

05/18/24

James S.

tutor
Adding these three numbers results in 136,061. Dividing by 3 gives you 45,353.667 and the whole number part of that is 3/2/2024 when formatted as a date. And that is exactly what Excel gave you! By the way, the fraction refers to a time of the day. In this case, 0.667 refers to 4 pm, which is exactly 2/3 of a 24-hour day.
Report

05/18/24

James S.

tutor
If I knew what your goal was, I am sure I could show you how to accomplish it.
Report

05/18/24

Mr A.

Sorry for the belated reply. No worries. I have figured out why my approach and solutions were wrong. If you wish, I can explain it you. If not, then all the best.
Report

06/05/24

James S.

tutor
I would be very interested in your explanation.
Report

06/05/24

Mr A.

Okay, then I will start by answering the fault of my second solution first. When I said that I could add the total number of days in between the dates and divide by 3, I am answering a question that I am not being asked. First, I am taking the difference in between the date values (number of days) and then averaging it. For ex., let’s suppose you are asked to take the average of 5, 7, and 10. The answer would be 7.5. What I’ve done essentially is take the difference in between 5, 7, and 10. Difference in between 5 & 7 = 2. The difference in between 7 & 10 = 3. Addition of the difference amounts: 2 + 3 = 5. Average (with the wrong difference amount, 3 instead of 2): 5/3 = 1.6666666. Now, it should’ve been (5/2) = 2.5, not (5/3) = 1. 6666666, since there are two values for the difference amounts instead of three, but that is what I’ve done. Not only did I divide with the wrong number of difference amounts for within average, but I also took a difference in between the date values, which is not being asked and just wrong.
Report

06/05/24

Mr A.

*Note: “->” is an arrow.
Report

06/05/24

Mr A.

Now, answering the fault for the first solution:
Report

06/05/24

Mr A.

Instead of using the reference point of 01/01/1900, I simply took the date as a reference point. What Excel does is that it converts the number into a “serial number” and then converts it back to a date, by counting the number of days since 01/01/1900. I applied the same logic but without “1900” reference. However, first I will explain how I came to a correct solution without using the “1900” reference and then explain what was the fault within my logic and solution. I applied the same logic as Excel does with “1900” as reference point. The dates were the following: 03/30/2023, 05/01/2024, 12/07/2024. I calculated the following values by using https://www.timeanddate.com/date/duration.html, not Excel 97. The values are off by 2. With 1900 as reference, the date 03/30/2023 would be 45,013 days. 05/01/2024 would be 45,411 days. 12/07/2024 would be 45,631 days. All these values are off by 2: 45,013 days, 45,411 days, and 45,631 days. With Excel, it would be the following: 45,015 days, 45,413 days, 45,633 days. Thus, https://www.timeanddate.com/date/duration.html vs. Excel would be the following: 45,013 days vs. 45,015 days, 45,411 days vs. 45,413 days, and 45,631 days vs. 45,633 days. Now for without the 1900 year reference, I performed these calculations by hand instead of using https://www.timeanddate.com/date/duration.html. 03/30/2023 would be 738,120 days. For the date 03/30/2023, the year 2023 would be 2022 (2023-1) years * 365 days = 738,030 days, since we have completed 2022 years with 365 days. Past the completion of year 2022, we are in the year 2023 the 1st day after the completion of year 2022. For being on month 3, it would be 2 * 30 = 60 days for the completion of 2 months, since we haven’t completed the 3rd month. Then add 30 days to all values to a total of 738,120 days. Now, it is not going to be an absolute exact answer but close enough since I am not factoring in leap years, and I am multiplying by 30 days instead of choosing between 28, 29, 30, or 31 days depending on what month it may be. Okay, again for date 03/30/2023 -> 738,120 days, 05/01/2023 -> 738,516 days, 12/07/2024 -> 738,732 days. Addition of all days: (738,120 + 738,516 + 738,732) = 2,215,368 days. Average = (Total/3) -> (2,215,368/3) = 738,456 days. 738,456 / 365 = 2023.167123 days. That means 2023 years have been completed, and we are on year 2024. 2023 * 365 = 738,395 days. 738456 – 738,395 = 61 days, or you can take the decimal amount of 0.16712 * 365 days = 60.999895 days. The 60.999895 amount tells us that two months have passed with some remainder, which means that we are on the third month and possibly the end of the first day of the 3rd month. 60.999895 is less precise than 61 days. 61 days/30 days = 2.033333333 months while 60.999895/30 = 2.033329833 months. Again, that means that two whole months have passed and now are on 1st day of the 3rd month. Thus, the answer is 03/01/2024. It is off by a day, which is pretty close.
Report

06/05/24

Mr A.

Now, here is a part that you might be able to help me with. According to https://www.timeanddate.com/date/duration.html, if I calculate the duration between 01/01/0001–03/30/2023, it is 738,610 days. However, my answer for date 03/30/2023 is 738,120 days. Difference: 738,610 – 738,120 = 490. I would’ve expected my answer to be larger. Am I missing something?
Report

06/05/24

Still looking for help? Get the right answer, fast.

Ask a question for free

Get a free answer to a quick problem.
Most questions answered within 4 hours.

OR

Find an Online Tutor Now

Choose an expert and meet online. No packages or subscriptions, pay only for the time you need.