Hello! I have some data for Spain's La Liga football league. While they are rank chronologically in the excel file, I believe the date formats are inconsistent (please see attached file). For example, in cell B2, the match between Albacete vs Osasuna takes place on 30 August 2003 (denoted by 30/08/03). However, in cell B12, the match between Osasuna v Valencia takes place on 2 September 2003 (denoted by 37661).
I realize that converting 37661 from Cell B12 into 02/09/03 is easy (right click and format cells > date). However, I have a problem with the data on cell B2. I have tried to use this formula on B2:
=REPLACE(B2,7,2,2003)
to try to convert 30/08/03 into 30/08/2003 and then formatting the new cell as a date, but it does not seem to work.
Moreover, I feel there must be an easier way to convert cell B2 into a date format than using the =REPLACE formula because that would force me to do this for every year, while in reality I just need to insert '20' after 'yy'.
It is important for me to have the dates be inconsistent so that with the database, I will be able to track individual teams as they progress through the season regardless of whether they play at home or away, and not having to resort to ranking them chronologically based on where they play. Thank you for your help!
Inconsistent Dates.xlsx
Bookmarks