Microsoft 365 and Office | Excel | For education | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
You are welcome.
excel datedif error on particular cells with wrong data type value error
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Anonymous
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
Microsoft 365 and Office | Excel | For education | Windows
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
10 answers
Sort by: Most helpful
-
Anonymous
2023-07-11T21:17:54+00:00 hi, thanks. this worked for me!
-
Ashish Mathur 102K Reputation points Volunteer Moderator2023-07-10T11:30:53+00:00 Thank you for sharing.
-
Andreas Killer 144.1K Reputation points Volunteer Moderator2023-07-10T10:33:01+00:00 =TEXTJOIN(" y ",TRUE,DATEDIF(A2,$E$1,{"y","ym"}))&" m"
Well,
this results are one of the reasons why Microsoft did not support DATEDIF anymore:
Andreas.
-
Anonymous
2023-07-10T02:05:18+00:00 Copy the column G to a new workbook and share with us.
For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.