<div>
No DATEDIF in Excel 2010 :(
</div>


<div>
I also have Excel 2010. If you copy paste the formula, it will work
</div>


<div>
Check below image:<br />
&nbsp;<a href="https://filedb.experts-exchange.com/incoming/2017/03_w11/1150438/DatedIf.JPG" target="_blank" title="DatedIF (23 KB)"><img alt="DatedIF" class="file-block lazyload" style="max-width: 423px;" data-src="https://filedb.experts-exchange.com/incoming/2017/03_w11/1150438/DatedIf.JPG" /></a>
</div>


DatedIf.JPG

<div>
DATEDIF gives an error in my client's office Excel 2010 Standard Edition. Tried it before as I develop in 2016.
</div>


<div>
What error does it give?<br />
DATEDIF works in Excel 2010 even though it doesn't exist in the function list.
</div>


<div>
Apparently not in the STANDARD edition. <br />
<a href="https://www.google.lk/search?q=excel+2010+datedif&oq=excel+2010+datedif&aqs=chrome..69i57.8436j0j7&sourceid=chrome&ie=UTF-8" rel="ugc">https://www.google.lk/search?q=excel+2010+datedif&oq=excel+2010+datedif&aqs=chrome..69i57.8436j0j7&sourceid=chrome&ie=UTF-8</a>
</div>


<div>
That proves the DATEDIF does work.
</div>


<div>
Alternatively, you can use below:<br />

<pre><code class="code-1 nolinks" id="code-20-42046289-1"><span>=TEXT(A1-A2,&quot;d&quot;) &amp; &quot; Days&quot;</span>
</code></pre>
</div>


<div>
Shums, that givs me a #VALUE! error.<br />
I would also need to calculate averages. So TEXT?
</div>


<div>
Give me your formula, you said A1-A2. What formula you are using for average and what range?
</div>


<div>
You may try this:<br />

<pre><code class="code-1 nolinks" id="code-20-42046506-1"><span>=TEXT(AVERAGE(A1:A2),&quot;d&quot;) &amp;&quot; Days&quot;</span>
</code></pre>
</div>


<div>
In addition, I am confused as to how you are getting 40867 as a result. That is the serial number for &quot;20 November 2011&quot;, to get that as a result there must be a large difference between the two dates that you are comparing.<br />
<br />
For example, &nbsp;A1 - B1 where <br />
<br />
A1 = 14 March 2017<br />
B1 = 24 April 1905<br />
<br />
gives result of 40867, number of days between the two dates.<br />
<br />
Alternatively, A1 - B1 where<br />
A1 = 14 March 2017<br />
B1 = 1941<br />
<br />
gives result of 20 November 2011 which formatted as a number rather than a date gives 40867
</div>


<div>
No comment has been added to this question in more than 14 days, so it is now classified as abandoned.<br />
<br />
I have recommended this question be closed as follows:<br />
Split: Rob Henson (ID: 42048152) , MacroShadow(ID: 42046021) and Shums (ID: 42046028)<br />
<br />
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.
</div>


<div>
<div class="content wysiwyg-content">
I have a simple formula that gives me the 'number of Days' between two dates. (eg. =A1-A2)<br />
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.<br />
<br />
How can I force Excel to think of the result of A1-A2 as 42? (and not 40867)<br />
<br />
Thanks
</div>
</div>


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

Excel 2010 - Average of days

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.