Date entered in wrong

[amikes]

My GNSS system uploaded all the dates wrong, so 2021-03-14 was entered as 2014-03-21. I cannot just reverse the year and day because it still recognizes it as 2014-03-21. Is there a way to fix this with a formula, or will I need to re-enter all 300 data points manually? Not sure if this makes sense.

 

[jtakw]

Hi,

If the values are actual dates in this format 2014-03-21 as yyyy-mm-dd
try this:

Book3.xlsx
	A	B
1	2014-03-21	2021-03-14
Sheet960

Cell Formulas
Range	Formula
B1	B1	=DATE(DAY(A1)+2000,MONTH(A1),YEAR(A1)-2000)

 

[jtakw]

Too late to edit.

Just in case, if the values are Text, then use B2 formula:

Book3.xlsx
	A	B
1	2014-03-21	2021-03-14
2	2014-03-21	2021-03-14
Sheet960

Cell Formulas
Range	Formula
B1	B1	=DATE(DAY(A1)+2000,MONTH(A1),YEAR(A1)-2000)
B2	B2	=DATE(2000+RIGHT(A2,2),MID(A2,6,2),MID(A2,3,2))

 

[amikes]

Thank you! Problem is solved. This was incredibly helpful.

 

[jtakw]

You're welcome, thanks for the feedback.

Actually, I just noticed you also have Time values showing in your screenshot, do you need that included in the conversion?

 

[amikes]

If there is a way to keep those as well, that would be greatly appreciated! They were entered in correctly

 

[jtakw]

If there is a way to keep those as well, that would be greatly appreciated! They were entered in correctly

Here you go:

Book3.xlsx
	A	B	C
1	2014-03-21 7:59	2021-03-14 7:59	< Format result cell custom yyyy-mm-dd h:mm
2			Or format of your preference
Sheet960

Cell Formulas
Range	Formula
B1	B1	=DATE(DAY(A1)+2000,MONTH(A1),YEAR(A1)-2000)+MOD(A1,1)

 

[amikes]

Here you go:

Book3.xlsx
	A	B	C
1	2014-03-21 7:59	2021-03-14 7:59	< Format result cell custom yyyy-mm-dd h:mm
2			Or format of your preference
Sheet960

Cell Formulas
Range	Formula
B1	B1	=DATE(DAY(A1)+2000,MONTH(A1),YEAR(A1)-2000)+MOD(A1,1)

Thank you so much for your help!! That works so perfectly

 

[jtakw]

You're very welcome.