I am requesting assistance on changing changing date/time format (i.e 0007-10-12T00:00:00.000) to YYYYMMDD (20071012) format in my Excel attachment.
Instructions are clear and I used visuals to describe my request.
Thank you.
I am requesting assistance on changing changing date/time format (i.e 0007-10-12T00:00:00.000) to YYYYMMDD (20071012) format in my Excel attachment.
Instructions are clear and I used visuals to describe my request.
Thank you.
I guess this might work?
=SUBSTITUTE("2"&MID(B2,2,9),"-","")
ragulduy,
Thank you so much for the formula! It worked for rows 2 to 7 because the year was in 2000s but it did not work for rows 8 to 12 because it is in the year 1900s. for the 1900s I revised your formula too =SUBSTITUTE("19"&MID(B9,3,8),"-","") .
Is there a way to merge formulas =SUBSTITUTE("2"&MID(B2,2,9),"-","") and =SUBSTITUTE("19"&MID(B9,3,8),"-","") together?
Select the column, goto Data>Text to columns..., selected Fixed widt.
Put a break points before the 3rd character and before the 'T'.
On the next step choose not to import the first and third columns and set the column data format for the 2nd column to YMD.
Click Finish.
You should now have 'real' dates which can be formatted however you like.
If posting code please use code tags, see here.
Norie,
Got it, thank you very much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks