Date format conversion

[jen0dorf]

Hi


Been given a spreadsheet where a column h is designated date o fburial and column I is designated year of Burial

Cell i2 shows jan-10 the formulae bar shows 01/01/2017 is formatted Custom mmm-dd

J2 is formatted General and has a value of 1862


I need to change it to show 10-jan-1862 or 10-01-1862 and be formatted as text.

Normally I'd convert the date and month to text format using

 =TEXT(I2,"dd-mmm")

(but any combination of this doesn't work.) and then merge the two cells

I then thought of duplicating the column and in one removing three characters from the left and in the other 4 characters from the right. I would then combine the colums to get thr format I need. But that does not work.

Would appreciate any ideas

thanks Ian

 

[Scott Huish]

Your date functions aren't working because Excel spreadsheets don't support dates before 1900.
However, if you just need to return it as a text string, then this should do it.

=TEXT(I2,"dd-mmm-")&J2

 

[jen0dorf]

Your date functions aren't working because Excel spreadsheets don't support dates before 1900.
However, if you just need to return it as a text string, then this should do it.

=TEXT(I2,"dd-mmm-")&J2

Thanks I'll give it a try

Ian