Forum Discussion
Data consolidation to compute duration in years between to different format of Dates.
Let's say your data are in columns A and B.
In C2, enter the formula =IF(LEFT(CELL("format",A2))="D",A2,DATE(A2,1,1))
This will replace a year with January 1 of that year. As an alternative, you might use July 1 - the middle of the year, as best guess of the date: =IF(LEFT(CELL("format",A2))="D",A2,DATE(A2,7,1))
Fill to the right to D2.
In E2, enter the formula =IFERROR(DATEDIF(C2,D2,"Y"),"---")
Select C2:E2, and fill down to the end of the data.
Let's say your data are in columns A and B.
In C2, enter the formula =IF(LEFT(CELL("format",A2))="D",A2,DATE(A2,1,1))
This will replace a year with January 1 of that year. As an alternative, you might use July 1 - the middle of the year, as best guess of the date: =IF(LEFT(CELL("format",A2))="D",A2,DATE(A2,7,1))
Fill to the right to D2.
In E2, enter the formula =IFERROR(DATEDIF(C2,D2,"Y"),"---")
Select C2:E2, and fill down to the end of the data.
I would like to thank you a lot for your prompt reply.
Thanks to your suggestion I solved the issue and you allowed me to save who knows how many hours of work on that excel sheet. You definitely made my day.
Thank you again for sharing your knowledge, very appreciated!