for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

Nessie Jan 2019 12:33pm, 7 Jan 2019 677 posts	Quote Pin Nessie Is there an easy way to convert 20181127 to 27-Nov-2018 for a whole column of values?
Sam Jelfs Jan 2019 12:53pm, 7 Jan 2019 143 posts	Quote Pin Sam Jelfs there is probably a much better way to do it, but I think... =DATE(LEFT(A1,4),LEFT(A1-(LEFT(A1,4)*10000),2),RIGHT(A1,2)) where A1 is the date you want to convert.
Sam Jelfs Jan 2019 12:55pm, 7 Jan 2019 144 posts	Quote Pin Sam Jelfs ah, no, that doesn't work....
Sam Jelfs Jan 2019 1:04pm, 7 Jan 2019 145 posts	Quote Pin Sam Jelfs try this instead... =DATE(LEFT(A1,4),IF(LEN(A1-(LEFT(A1,4)10000))=3,LEFT(A1-(LEFT(A1,4)10000),1),LEFT(A1-(LEFT(A1,4)*10000),2)),RIGHT(A1,2)) I had forgotten to account for months starting with a 0, as in 20180127
run free Jan 2019 1:07pm, 7 Jan 2019 3,320 posts	Quote Pin run free @Sam - nearly there: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Format the cell to what ever date format you want or for an all in one use the TEXT function: =TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)), "dd-MMM-YYYY")
run free Jan 2019 1:11pm, 7 Jan 2019 3,321 posts	Quote Pin run free The alternative to this is to use the TextToColumns command in the Data tab: a) Choose a fixed width b) Ignore second dialog box c) Choose the YMD format However formulas are better for transparency
Sam Jelfs Jan 2019 1:15pm, 7 Jan 2019 146 posts	Quote Pin Sam Jelfs You see, when I looked I couldn't find a mid function, hence my hack around it... I'm going back to matlab and it's nice datenum function.
um Jan 2019 2:18pm, 7 Jan 2019 721 posts	Quote Pin um I'd go for the text to columns approach ...
Nessie Jan 2019 3:20pm, 7 Jan 2019 678 posts	Quote Pin Nessie Perfect RF - thanks so much. I had tried the LEFT/MID/RIGHT option but without the date bit I had to split it up.
GlennR Jan 2019 10:32am, 15 Jan 2019 40,202 posts	Quote Pin GlennR I am going on an R training course this afternoon. I am pulsing with excitement.