Jul 30 2022 03:16 PM
I'm having a problem with the DatedIf formula (below). It keeps on returning a "0". Any help would be appreciated. Thanks
Jul 30 2022 10:13 PM - edited Aug 01 2022 04:44 AM
Jul 30 2022 11:44 PM - edited Aug 01 2022 06:50 AM
@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 .
Aug 01 2022 04:42 AM
@Joe User Thanks for correcting by bad. Will delete my previous post in a sec.