Excel date issue

[stepej]

Hi

I'm not too fluent in excel and have hit a bit of a problem...

In a nutshell I have to copy 5 columns from one spreadsheet to a second spreadsheet
One of the columns is formatted as DATE in the first spreadsheet but needs to be formatted as TEXT in the spreadsheet i'm copying to

Is there a 'Paste Special' selection i can use that allows this?, or is there some other (easy) way to do this?
Every time i try it either reformats the column in the second spreadsheet to DATE or i just get a string of numbers where the date should be

Any help would be truly appreciated

[Prolex-UK]

You can reformat the numbers to a date by right clicking in the cell going to date select date format you want.

Or highlight the whole column

[p4cks]

I had a similar issue which was somehow remedied by copying the column contents, pasting them into Notepad and then copying them again and pasting back in to Excel.

Seemingly it strips/cleans the formating from the data

[ATG]

As an alternative, could you keep the dates as dates in the second spreadsheet, but change the way that spreadsheet formats dates so they appear in whatever format you want?

In general you'll be better off if Excel knows that the contents of a cell is a date, if that's what it really is. If you start trying to handle dates as strings, it often ends in tears when the string becomes ambiguous or badly formed.

[oxford drinker]

Can't you just "paste values" ?

[stepej]

Thanks for the prompt replies....

The column need to be TEXT as the second spreadsheet is being imported to a file that is expecting it as TEXT

paste VALUES converts the column to DATE

The extra step of copying to notepad would work but isn't an option here i'm afraid

What is annoying me is that by some quirk of fate i managed to do it the first time i tried, so thinking it was easy didn't make notes, then when i've tried again, i've failed every time.

30 odd years in IT and things like this still frustrate me

[mmm-five]

If it's a date that you want in text format then use

=TEXT(A1,"dd-mmm-yy")

So if the date in A1 is 01-Jan-2021 (44197 in serial), then it will convert it to a text string of '01-Jan-21'.

You can adjust the formatting in the formula to suit how you want to display the date.

Then just copy and paste values to remove the formulae.


...or you could just use the Text to Columns function in the Data toolbar to tell the info to change from DATE format to TEXT format

Edited by mmm-five on Thursday 25th November 15:10

[jonsp]

No need for for the notepad step. Don't use copy paste for this - use a formula like this = TEXT(Your Cell, "format you want date in")

For example you have Todays date in cell A1 and you want this date as text in A2 like this 25 Novermber 2021

=TEXT(A1, "dd mmmm yyyy")

Obviously if you need to do a whole range you can copy/paste the formula not the date itself. Make sense?

[stepej]

mmm-five said:

...or you could just use the Text to Columns function in the Data toolbar to tell the info to change from DATE format to TEXT format

Edited by mmm-five on Thursday 25th November 15:10

Cheers... i think this'll be the way to go

I did notice that function but wasn't sure about it...
Maybe i should have shown more of an interest in pc applications when the opportunities arose

Thanks everyone for the replies

[paulrockliffe]

oxford drinker said:

Can't you just "paste values" ?

I think this won't work based on the OPs description because the Value in the source table isn't a date, it's a number formatted as a date. So when you do this it pastes as that number.

OP, what are you doing with the data? I spend an inordinate amount of time fixing st because people format things that aren't text as text, so your question has me on edge and the voice in my head is screaming at me to tell you not to do it that way.

[boyse7en]

Not very helpful to the OP, but it made me laugh

[Sebo]

boyse7en said:

Not very helpful to the OP, but it made me laugh

[mmm-five]

boyse7en said:

Not very helpful to the OP, but it made me laugh

It's like a cross between Reddit and 4Chan!