Converting date field to txt

[djbe17]

Hi all ,
tricky formatting problem i'm trying to work through.
We use an external data set ( SFG20) which have used the format of 00-00 to record records.
When extracted to use in excel tables these are recognised as dates and converted - so a record with 06-04 is converted to 06-Apr ( with value of 45022 )

i have tried many options of formatting to DD-MM, and then copy as values but although this looks OK values with cells are still incorrect.

Added to this some SFG20 codes are fine as 44-04 is imported correctly - the image below i hope make this clearer, green cells are as expected, red are cells i need to correct.

Any thoughts on a formula or method to copy paste to correct this?

Many thanks all.

 

[Joe4]

To convert a date like "29-Aug" to the text value "29-Aug", you can use the following formula (for an example value in cell A2):

=TEXT(A2,"dd-mmm")

If you wanted to overwrite the value, you could use VBA. The VBA equivalent of the TEXT function is FORMAT.

 

[jdellasala]

Assuming the SFG20 data is in some other format like a text file like this:

06-04
30-03
23-19
29-08
44-04
44-04
44-04
44-04
44-04
44-04
44-04
23-02
23-02
16-05
20-12
63-06
63-06

Bringing the file in through Power Query would initially result in a table with that as text, however if any of the cells are modified, they'd turn into dates if possible. What would work is to add an apostrophe before the data, then it would always be text.

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Temp\SFG20.txt"), null, null, 1252)}),
    AddedCustom = Table.AddColumn(Source, "Custom", each "'" & [Column1], type text)
in
    AddedCustom

That results in this table:

Book1
	A	B
1	Column1	Custom
2	06-04	06-04
3	30-03	30-03
4	23-19	23-19
5	29-08	'29-08
6	44-04	'44-04
7	44-04	'44-04
8	44-04	'44-04
9	44-04	'44-04
10	44-04	'44-04
11	44-04	'44-04
12	44-04	'44-04
13	23-02	'23-02
14	23-02	'23-02
15	16-05	'16-05
16	20-12	'20-12
17	63-06	'63-06
18	63-06	'63-06
Sheet2

NOTE: The haphazard justification in the display above is an XL2BB anomaly. In the actual table, ALL values are Left Justified since they are all considered Text, but as noted some of the values in Column A would convert to dates if edited.
What you don't see above is that once the data is "seen" by Excel as text, that leading apostrophe "disappears":

The down side is if Excel has reformatted the modified cell to hide the first apostrophe. On a Refresh, modified cells will end up with two apostrophes and will subsequently always display that way.

Clearing the cells doesn't fix it either, only deleting the column does. Of course, simply adding an apostrophe manually to the text file would work too!

Good news though, there's new options coming that might help. There are new Automatic Data Conversion options in the works:

I honestly don't know if that would help, but it's promising.