We help IT Professionals succeed at work.

Excel 2010 - Average of days

80 Views
Last Modified: 2017-04-02
I have a simple formula that gives me the 'number of Days' between two dates. (eg. =A1-A2)
Say the result is 42. When I calculate the average of these results Excel thinks the value is the Date Serial, rather than 42. So I get the wrong answer.

How can I force Excel to think of the result of A1-A2 as 42? (and not 40867)

Thanks
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Format cell to general
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
No DATEDIF in Excel 2010 :(
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
I also have Excel 2010. If you copy paste the formula, it will work
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Check below image:
 DatedIF

Author

Commented:
DATEDIF gives an error in my client's office Excel 2010 Standard Edition. Tried it before as I develop in 2016.
CERTIFIED EXPERT

Commented:
What error does it give?
DATEDIF works in Excel 2010 even though it doesn't exist in the function list.
CERTIFIED EXPERT

Commented:
That proves the DATEDIF does work.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Alternatively, you can use below:
=TEXT(A1-A2,"d") & " Days"

Open in new window

Author

Commented:
Shums, that givs me a #VALUE! error.
I would also need to calculate averages. So TEXT?
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Give me your formula, you said A1-A2. What formula you are using for average and what range?
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You may try this:
=TEXT(AVERAGE(A1:A2),"d") &" Days"

Open in new window

Finance Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
In addition, I am confused as to how you are getting 40867 as a result. That is the serial number for "20 November 2011", to get that as a result there must be a large difference between the two dates that you are comparing.

For example,  A1 - B1 where

A1 = 14 March 2017
B1 = 24 April 1905

gives result of 40867, number of days between the two dates.

Alternatively, A1 - B1 where
A1 = 14 March 2017
B1 = 1941

gives result of 20 November 2011 which formatted as a number rather than a date gives 40867
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
No comment has been added to this question in more than 14 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:
Split: Rob Henson (ID: 42048152) , MacroShadow(ID: 42046021) and Shums (ID: 42046028)

If you feel this question should be closed differently, post an objection and a moderator will read all objections and then close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.