I am trying to find out difference between two days "xx years xx days xx hours xx minutes" format. My start date is in A column and end date is in B. Calculating difference in C column.
I am using B1-A1 formula in C column and using custom format "yy" years "dd" days "hh" hours "mm" minutes" in C column. It works fine if the start and end years are different. But if it is same, C column reporting incorrect years and days. (showing as 99 years and 30 days).
How to fix it ?
Your B2 - A2 formula is actually getting the correct result — it is a dateserial value that represents the difference between the two moments in units of days.
The problem is with the custom number format you are using. It refers to calendar time, while you would need elapsed time. Unfortunately, Google Sheets apparently only supports elapsed time in units of hours, minutes and seconds.
If a text string result is OK with you, use this:
=datedif(A2, B2, "y") & " years " & datedif(A2, B2, "d") - 365 * datedif(A2, B2, "y") & " days " & text(B2 - A2 - int(B2 - A2), "HH ""hours"" mm ""minutes""")
The formula assumes that all years are 365 days long, which will not be correct when the date span crosses a leap day.
To get the average elapsed time, evaluate =B2 - A2 for all rows with an array formula, then calculate the average of those results, and finally format the result to your liking, like this:
=arrayformula( average( if( B2:B + A2:A, B2:B - A2:A, iferror(1/0) ) ) )
=datedif(0, G2, "y") & " years " & datedif(0, G2, "d") - 365 * datedif(0, G2, "y") & " days " & text(G2 - 0 - int(G2 - 0), "H ""hours"" m ""minutes""")
See cells F2:G2 in your sample spreadsheet.
See this answer for an explanation of how date and time values work in spreadsheets.