You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
Hi there,
I'm trying to use the 'DATEDIF' function to determine the number of days since a start date and the current date as per the image. In the 'Age' cell I'm using the formula with the Current Date below as the start date, and the 'Purchased' date as the End date and Days and the method. I get an error. What am I doing wrong - it says it's referencing its own cell but I can't see how as the result cell is separate from the various source cells.
iMac, macOS 10.13
JP from EG wrote:
Hi there,
I'm trying to use the 'DATEDIF' function to determine the number of days since a start date and the current date
In Numbers with start date in C2, and end date in M1, you can just use
=M1-C2 to get the number of days.
You can also just use =TODAY()-C2.
If you need the number of days as decimals for further calculations you can use:
=DUR2DAYS(M1-C2) or =DUR2DAYS(TODAY()-C2)
SG
JP from EG wrote:
Hi there,
I'm trying to use the 'DATEDIF' function to determine the number of days since a start date and the current date
In Numbers with start date in C2, and end date in M1, you can just use
=M1-C2 to get the number of days.
You can also just use =TODAY()-C2.
If you need the number of days as decimals for further calculations you can use:
=DUR2DAYS(M1-C2) or =DUR2DAYS(TODAY()-C2)
SG
HI JP,
Note the colour highlight in ALL CELLS of column D, including the cell (D2) containing the formula. That is the cause of the 'self reference' error.
'
The D is being read as a cell/column reference because it is not enclosed in a pair of double quotation marks, as seen in the image below, identifying it as the text code telling the formula to report the difference in days.
Note also the small triangle beside the "D". Clicking that triangle will open a four item menu from which you can select the unit used to express the DATEDIF.
The cell reference (Table 4::L1), pointed out by Ralf, will also trigger an error triangle in D2, but the error message for that error will be one for a 'wrong type of value', not the self reference error message seen in your screen shot.
Regards,
Barry
I'm glad to say that all the above methods work. It seems that when I first used the 'DATEDIF' function, I did not click on the right components in the equation bar which appears to automate various parts rather than requiring a more manual input. All good.
Thanks for both the suggestions. I will try each in the context I need.
Im still curious why the error message said “This formula can’t reference its own cell . . . . “ when the cell was only the result and not used for the equation components. Perhaps this is just the reaction when I seem to have selected a text entry for the ‘start date’ instead of a ‘date & time’ value?
No, if you subtract dates in cells formatted as Text (instead of Date & Time) Numbers should still happily do the date math for you.
Not sure about that self-reference error as can't see all your formulas.
Do you still see it if you follow the suggestions above?
SG
Hi JP from EG,
Please check your formula.
Cell L1 is a text, I think it should be cell M1.
Ralf
I’m away from home and the file now till Wednesday morning (GMT) so will report back then after trying all the suggestions.
Date difference
