Date format

[rae30]

I have a data dump that I have to format in Excel and I'm having difficulty formatting the date.

For example 01/05/2015 formats to Jan 5th 2015, however it should be May 1st. How do I fix this.

 

[deletedalien]

in other words if you have in cell A1 01/05/2015 and you want it to be May 1st

in B1 =MONTH(A1)
In C1 =DAY(A1)
In D1 =YEAR(A1)

And lastly F1

=DATE(D1,C1,B1)

Additionally you could shorten this by just using


=DATE(YEAR(A1),DAY(A1),MONTH(A1))

 

[gaz_chops]

in other words if you have in cell A1 01/05/2015 and you want it to be May 1st

in B1 =MONTH(A1)
In C1 =DAY(A1)
In D1 =YEAR(A1)

And lastly F1

=DATE(D1,C1,B1)

Text to columns as above, will do the same, without the helper columns.

 

[deletedalien]

Text to columns as above, will do the same, without the helper columns.

true...... IF the original is formatted as text... But if excel recognizes it as date then it will just leave it as is... <--- never-mind this last part i JUST tested it and i was wrong

 

[deletedalien]

so yeah just do text to columns and change the date format

 

[Brad_Miller]

Have you checked the use 1904 date option?

its under excel options, advanced, scroll down to "when calculating this workbook" select or unselected 1904 date option