Custom Number Format: Convert to Date (yyyy-mm-dd)

**xtinct2** · 10-25-2017, 01:22 PM

Hi,

I have a sheet w/ multiple columns that list dates. However the dates are written in the format of '6012017' (June 1st, 2017), and '11012016' (November 1st, 2016).
This type of format seems to be 'mddyyyy' -- where the month could be 1 or 2 digits, the day is always 2 digits, and the year is always 4 digits.

I know how I would manipulate this via a formula, but for this exercise I can't go that route.

*** What Custom Number Format (in the 'Format Cells' dialogue box) should I input to convert all columns to to the 'YYYY-MM-DD' format? ***

Is this possible?

Thank you!

**davesexcel** · 10-25-2017, 01:31 PM

If your values are truly dates, then I would think the current format would be something like, m"0"dyyyy.
I assume they are not entered as dates because you are asking the question and your custom format is not working?

**xtinct2** · 10-25-2017, 01:33 PM

Originally Posted by davesexcel

If your values are truly dates, then I would think the current format would be something like, m"0"dyyyy.
I assume they are not entered as dates because you are asking the question and your custom format is not working?

correct, they're not input as dates -- is there a workaround?
Thanks

**popipipo** · 10-25-2017, 01:54 PM

Please Login or Register  to view this content.

Try this formula with celformat 'YYYY-MM-DD'

**davesexcel** · 10-25-2017, 02:12 PM

Originally Posted by popipipo

Please Login or Register  to view this content.

Try this formula with celformat 'YYYY-MM-DD'

I thought you had it. When you have 2-digit days it shows the wrong result, I was trying to find the first zero, ie: =FIND(0,A1)-1 will give you the the month integer =LEFT(A1,FIND(0,A1)-1)

**xtinct2** · 10-25-2017, 02:16 PM

i've written a formula that works -- but i'm asking whether it's possible to accomplish w/o the use of a formula (nor VBA)?

**popipipo** · 10-25-2017, 02:18 PM

I don't think it can

**63falcondude** · 10-25-2017, 02:18 PM

Try Text to Columns.

We can't test it since we haven't seen your data, but try highlighting the column that the "dates" are in and then:

Data > Text to Columns > Next > Next > Date: MDY > Finish

I'm not sure if this will work with inconsistent data though.

**63falcondude** · 10-25-2017, 02:23 PM

To be clear, you really should convert these "date" values into actual dates, not try to format them as is to look like dates.

Simply trying to convert something to look like a date may bring issues if you plan on doing anything with the data.

**davesexcel** · 10-25-2017, 02:25 PM

Originally Posted by xtinct2

i've written a formula that works -- but i'm asking whether it's possible to accomplish w/o the use of a formula (nor VBA)?

Format cannot change the value

**xtinct2** · 10-25-2017, 02:29 PM

There are vast amounts of columns with dates in these formats, so I can't use a formula b/c it'd require having a formula column for each static column.
The result doesn't need to have propertices of a date -- just needs to be in the yyy-mm-dd format -- even if excel only recognizes it as text, that's fine.

Doesn't see like it's possible, though
Thanks all!

**63falcondude** · 10-25-2017, 03:12 PM

You can create a column using a formula and then copy/paste values. Then replace the original column with the actual date values.

That way there are no additional columns and your date columns are actual dates (which you can then format however you'd like.

**Ravisca2003** · 10-25-2017, 03:20 PM

Originally Posted by xtinct2

There are vast amounts of columns with dates in these formats, so I can't use a formula b/c it'd require having a formula column for each static column.
The result doesn't need to have propertices of a date -- just needs to be in the yyy-mm-dd format -- even if excel only recognizes it as text, that's fine.

Doesn't see like it's possible, though
Thanks all!

Sorry but why you are not looking forward to vba . I saw you mentioned some where abt that.

**davesexcel** · 10-25-2017, 03:41 PM

If you don't want to save the book as a macro workbook, you can have the code in a different workbook and run it from there.

Custom Number Format: Convert to Date (yyyy-mm-dd)

LinkBack

Thread Tools

Rate This Thread

Display

Custom Number Format: Convert to Date (yyyy-mm-dd)

Re: Custom Number Format: Convert to Date (yyyy-mm-dd)

Re: Custom Number Format: Convert to Date (yyyy-mm-dd)

Re: Custom Number Format: Convert to Date (yyyy-mm-dd)

Re: Custom Number Format: Convert to Date (yyyy-mm-dd)

Re: Custom Number Format: Convert to Date (yyyy-mm-dd)

Re: Custom Number Format: Convert to Date (yyyy-mm-dd)

Re: Custom Number Format: Convert to Date (yyyy-mm-dd)

Re: Custom Number Format: Convert to Date (yyyy-mm-dd)

Re: Custom Number Format: Convert to Date (yyyy-mm-dd)

Re: Custom Number Format: Convert to Date (yyyy-mm-dd)

Re: Custom Number Format: Convert to Date (yyyy-mm-dd)

Re: Custom Number Format: Convert to Date (yyyy-mm-dd)

Thread Information

Users Browsing this Thread

Similar Threads

Help require to change a year format to custom yyyy-yyyy format

[SOLVED] Convert NUMBER Series entered as TEXT To DATE FORMAT mm/dd/yyyy

[SOLVED] How to Convert date format ( DD/MM/YYYY) into MM/YYYY

convert date in yyyy/mm/dd format

[SOLVED] Convert date format to (dd/mm/yyyy)

[SOLVED] Convert Date Format from yyyy-mm-dd to dd mmm yy using VBA

[SOLVED] Imported data contains strings dd.mm.yyyy how can I convert to date format dd/mm/yyyy inVB

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions