excel datedif error on particular cells with wrong data type value error

Question

hi there,

bit of a weird one but i have a table and im trying to calculate the date difference between two dates - basically trying to calculate the age of a device. in one column is the registered date and im subtracting it from todays date and then formatting the output in months and years in another column. the formula works fine in certain cells but in others i get a wrong data type error so not sure what is going on. would be grateful for some insight

G7 G8

10/06/2021  2y 0m 
23/03/2022  #VALUE!
04/12/2022  0y 7m 

and formula used is

=DATEDIF(G7,$N$4,"y") & "y " & DATEDIF(G7,$N$4,"ym") & "m "

N4 cell basically holding todays date. the cells and columns have been formattted to date type  in english UK in the pattern dd/mm/yyyy

Answer 1

Hi,

This fomula in cell B2 works fine for me

=TEXTJOIN(" y ",TRUE,DATEDIF(A2,$E$1,{"y","ym"}))&" m"

Hope this helps.

Answer 2

sorry ISTRUE and ISNUMBER returns false for cell 6 and TRUE for cell 7

Answer 3

Hi thanks for your quick response.

ISTEXT (G7) returns false as does ISNUMBER for the same cell of G7 however if i type the same commands for the cell below it say G8 i get TRUE both times.

The data in the G column has been pulled from active directory and some data is in short date format and other cells have a timestamp attached to it so i tried to clean up the column by formatting it into a dd/mm/yyyy format but it didn't trim those fields that had a time stamp attached to them.

im not sure if this makes any difference so not really sure what to do. the formula im writing is going in column H cell 6

	Column G	Column H	Column I
Cell 6	10/06/2021	2y 0m
Cell 7	23/03/2022 15:46:34	#VALUE!
Cell 8	04/12/2022	0y 7m

Answer 4

The cell format does not matter.

And DATEDIF even accepts text parameters, as long as they can be converted to a numeric date based on the system configuration.

The "cell" labels in your posting are confusing. G7 and G8 should be vertical cells in the same column, not two columns. And G7 and G8 cannot refer to 3 separate pairs of cells.

Assuming G7 refers to the data that appears to be 23/03/2022, I would conclude that G7 contains text that Excel cannot convert to a numeric date based on the system configuration.

Looks can be deceiving, and again, the cell format does not matter.

Confirm that =ISTEXT(G7) returns TRUE, and =ISNUMBER(--G7) returns FALSE. Note the double negation operators before G7.

Then, look at your system configuration (Region and Language control panel) to confirm that the Short Date form is not d/m/yyyy.