Forum Discussion
Date Formatting
I did that, and it converts to text fine.
I think I should have gave more info.
The report I am working with is a list of individuals, training courses, and due dates. The due dates are expressed in a general formatted column as mmmyy, with no day. I am trying to create a product that I can copy the data into, and have a series of conditional formatting and other things tell me who is due/overdue for what so I can more easily see who I need to schedule for what.
If the dates are not in a date format excel will recognize, this will not work.
If I were to try to change APR19 to a date format excel would think 19 is the day when it is the year. What would help the most is if I can somehow convert APR19 (along with all the other dates in the column (IE: MAR18, DEC17, etc.)) to 1APR19 (and 1MAR18, 1DEC17, etc) I could make progress.
Converting general to text doesn't really help. Thank you for the reply though. I didn't know CTRL+1 was a shortcut for Format...I will definitely be using that in the future. :)
The formula is =text(A2,"ddmmmyyyy")
Look to text in A2 and change to text
d=day
m=month
Y=year
or you make "dd-mmm-yyyy"
always use " to start,
A.J. Buck wrote:
I did that, and it converts to text fine.
I think I should have gave more info.
The report I am working with is a list of individuals, training courses, and due dates. The due dates are expressed in a general formatted column as mmmyy, with no day. I am trying to create a product that I can copy the data into, and have a series of conditional formatting and other things tell me who is due/overdue for what so I can more easily see who I need to schedule for what.
If the dates are not in a date format excel will recognize, this will not work.
If I were to try to change APR19 to a date format excel would think 19 is the day when it is the year. What would help the most is if I can somehow convert APR19 (along with all the other dates in the column (IE: MAR18, DEC17, etc.)) to 1APR19 (and 1MAR18, 1DEC17, etc) I could make progress.
Converting general to text doesn't really help. Thank you for the reply though. I didn't know CTRL+1 was a shortcut for Format...I will definitely be using that in the future. :)
and " to end the name
Thank you. Unfortunately that still results in excel thinking that the 19 in APR19 is the day (remember format is mmmyy).
I need to format an entire column (up to 8,000 cells) which have text in them in a mmmyy format.
I need to convert that text to the examples below
APR19 to 1 APR 2019
MAR18 to 1 MAR 2018
JUN17 to 1 JUN 2017
Is there a way I can append a 1 to the beginning of every cell? That might simplify my problem
If the existing dates that you have are already formatted as texts in this format (MMMYY) then you can append 1 to each one of them by using this formula:
=DATE("20"&RIGHT(A1,2),MONTH(A1),1)And then the apply the Custom format that I have suggested in the previous answer!
Good luck!
