Feb 11 2018
07:26 PM
- last edited on
Jul 12 2019
11:02 AM
by
TechCommunityAP
Feb 11 2018
07:26 PM
- last edited on
Jul 12 2019
11:02 AM
by
TechCommunityAP
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.
Feb 11 2018 08:29 PM - edited Feb 11 2018 08:30 PM
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!
Feb 11 2018 11:29 PM
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. :)
Feb 12 2018 12:14 AM
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
Feb 12 2018 06:53 AM - edited Feb 12 2018 06:55 AM
Ok, I have another solution!
Get rid of the text format that I have suggested in the previous answer!
Take the dates which are formatted as MMMYY, and put them in Excel.
After Excel convert the years to days, you can fix that by using this formula
=DATE(20&DAY(A1),MONTH(A1),1)
The result of this formula is an actual date, not text!
Not you got the correct dates, but the format of them may not as you want (DMMMYY), but you can convert them to this format as follows:
Feb 12 2018 06:54 AM
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
Feb 12 2018 07:06 AM - edited Feb 12 2018 07:13 AM
GREAT! that did what I want!
The only difficulty now is ill have to create a new column in the table for the new values to apply to. Unless you know of a way I can run a script on a column without having the equations embedded in the cells themselves...
Thank you very much for your help!
Feb 12 2018 07:16 AM
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!
Feb 12 2018 09:01 AM
By the way, I tried Power Query (aka Get & Transform) - it transforms correctly, just couple of clicks (get source data from table/range and load the result back into the Excel sheet)
with very simple code automatically generated
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}) in #"Changed Type"
Feb 12 2018 02:15 PM
Feb 13 2018 01:39 AM
I see, known situation. And most probably your next version will be 2019.
Bit more about two-component dates. Excel interprets them as day/month of the current year and if no success with that shifts on first day of the month/year. For example,
Apr30 will be converted to 2018-04-30, but
Apr31 to 1931-04-01
To my knowledge there is no way to change such behavior by settings, the workaround could be as Haytham suggested or similar. At the same time with regular updates and refreshing using helper column looks bit annoying, perhaps VBA script could help to update in-place with one click, but that's not my expertise.
Feb 13 2018 08:51 AM
I have another solution for you to fix the column of date in place without using a formula in a helper column!
You can use it if the existing dates that you have are already formatted as texts in this format (MMMYY)!
Please find the attached file, and download it!
It contains a macro that you can run to fix the column of dates in place.
To use it, highlight the dates and go to View >> Macros >> View Macros.
Find the macro that named "convert", and then hit Run button.
Remember to highlight the dates before you run the macro.