Forum Discussion
Date Formatting
Greetings,
I have a column of dates from a system which is in a GENERAL format. The problem is, the system does not provide a day in the date; data is in mmmyy format (IE: APR19).
The problem is every time I try to adjust the format Excel assumes the 19 is the day and changes the year to this year (IE 19 APR 2018). There is no way to change the output in the system.
I have to refresh this data weekly, as well as filter it so that people can understand what it means. there are more than 8,000 lines in the column. Needless to say I spend a lot of time filtering this data.
If I can get the dates to reflect the first of each month I will be happy (IE 1APR19), but I am not sure how to go about doing this.
Thank you for any assistance you can provide.
Hi,
Try to format the column of date as Text as follows:
Select the column of dates, press Ctrl+1, select Text, and then hit OK!
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
