DATEDIF PROBLEM

Occasional Visitor

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

 

 

Elaine_Work_0-1659219217123.png

 

3 Replies

@Elaine_Work 

 

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 box.net/files; others like dropbox.com.  You might like onedrive.live.com 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.