Occasional Visitor

I'm having a problem with the DatedIf formula (below).  It keeps on returning a "0".  Any help would be appreciated.  Thanks





3 Replies



See @Joe User reply below

@L z.  wrote:  ``you must convert it to a Number``


Sorry, but that is incorrect.  Note that DATEDIF("1/1/2022", "8/1/2022", "m") works just fine, returning 7 as expected.


In fact, many Excel functions that expect numeric parameters accept text, as long as Excel can interpret the text as a number (including dates, percentages, currency).


Of course, we run the risk that a string that works in one language does not work in another because of regional differences.


@Elaine_Work  claims that the DATEDIF returns 0.  (Note Elaine:  there are no quotes around 0 because it is numeric, not a string.)  But in fact, I believe it should return #NUM because the second parameter (zero) is less than the first parameter (24699, which is 8/15/1967).


That is indicated by the fact that the "fx" dialog box shows that the result is "Formula Result =", not "Formula Result = 0".


(I'm surprised that the "fx" result is not "Formula Return = #NUM!".  But I confirmed that it is not!)


If the actual cell value displays 0 (sometimes it differs from what the "fx" feature shows), my guess is:  (1) Manual Calculation is selected; or (2) there are circular references; or (3) for some other error reason, Excel does not recalculate the cell.


@Elaine_Work  , if that does not point you in the right direction, I suggest that you attach an Excel file that demonstrates the problem.


If the forum does allow that for you yet, upload the Excel file to a file-sharing website, and post the download URL (*) in a response.  I like; others like  You might like because it use the same login as this forum.


(*) If the forum does not allow you to post a URL, manually "spell" it out, for example:

techcommunity dot microsoft dot com /t5/excel/datedif-problem/m-p/3586855 .

@Joe User Thanks for correcting by bad. Will delete my previous post in a sec.