Data consolidation to compute duration in years between to different format of Dates.

[Bezz8]

Hello everyone, 

I am trying to calculate the period of time between dates that have been set with different formats (some times year only, other month-year, others the extended date). For sake of clarity and for a better understanding of my query, please refer to the below print screen:

Since the file is including 200k raws, does someone has any idea on how to homogenize the dates format in order to smoothly compute the timing between start & end date? 

Thank you a lot in advance to the ones that will provide useful tips.  

[#2 · Accepted solution]

@Bezz8 

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.

[#3]

Hello Hans,

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!