-
stupid thing to try... add another column and then add zero to each date, can sometimes force it to recognise dates as a number rather than text.
-
Excel is awful for dates, it always tries to be smart and autoformat values and invariably causes some fuckup.
I'm assuming the values you're pasting are all in a consistent format of text like "29 January 2021"? If so, I'd copy and paste them AS TEXT into Excel, then use DATEVALUE in another column to convert them all to a date. (DATEVALUE converts text into a date, not a date into something else, which is why you're getting an error.)
Once you've got everything in a column, and Excel is recognising them all as dates, you can sort in chronological order.
Confusingly, the number format in Excel is just a display layer, you can change the number format or date format without affecting the underlying value. Which sometimes makes it hard to see what the underlying values are.
-
seems to to be in the sequence of the day
Makes me think there's a space before or after it (or some other annoying value that's not showing easily), and it's seeing it as text. Can you trim the contents before trying to format?
datevalue(25/02/2015) gives me an error (guessing now)
The value needs to be a string there, i.e. datevalue("25/02/2015")
You could always try rebuilding the string...
If annoying field is in A1 and looks like 23/05/2017try
=DATEVALUE(RIGHT(A1,4)&"-"&MID(A1,4,2)&"-"&LEFT(A1,2))then once you have a separate column of 'good' dates then you can copy/paste values back to where you need to remove the formulae and just keep the good dates
-
Text to columns and identifying that column as date fixes that most of the time.
You are reading a single comment by @ChainBreaker and its replies.
Click here to read the full conversation.
Sam_w
Drakien
duncs
aggi
@ChainBreaker
Hey hivemind
I am struggling with excel and was hoping someone could help. This is dumb, but i cannot get it sorted.
I have copied and pasted dates (from my bank statements on a pdf): It starts off 29 January 2021
and copies as 29 January 2021. Great. BUT some go from 30 January 2017 which swaps to 30/01/2017.
Anything in the dd/mm/yyyy format is not recognised as a date. And it is not possible to format that number. NOTHING ive done has worked.
So i have 2 sets of number ranges.
03 February 2021 - format that can be formatted and put into age sequence.
03/03/2015 - non formattable and seems to to be in the sequence of the day (ignoring the month and year)
23/05/2017
25/02/2015
*I have (obvioulsy) tried: Format Cell>Number>anything else, and that will remain as 30/01/2017
*I have tried to use the format painter from everywhere including both the working date as well as the other columns.
*I have tried changing the format to custom then back to something else.
*I have tried to protect and unprotect the sheet
Is there any reason why i cannot format it? All i want to do is sort it in cronlogical order.
Anyone ever have an unformatable field esp a date?