Converting troublesome date format

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Apr 22, 2017

• #2

Sean

Try this in a cell, it should produce valid date value that you can format as you like.

=DATEVALUE(LEFT(B31,10))

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,463

Office Version

1. 365

Platform

1. Windows

• Apr 22, 2017

• #3

Norie said:

Sean

Try this in a cell, it should produce valid date value that you can format as you like.

=DATEVALUE(LEFT(B31,10))

Click to expand...

Since single digit day and month numbers have leading zeroes, it looks like this formula could also be used as well...

=0+LEFT(A1,10)

 

Last edited: Apr 22, 2017

Upvote 0

FDibbins

Well-known Member

Joined

Feb 16, 2013

Messages

6,723

• Apr 22, 2017

• #4

Is that always the format your data is in - yyyy-mm-ddblahblah?
4 digit year, 2 digit month, 2 digit day (or is it day/month?)

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,463

Office Version

1. 365

Platform

1. Windows

• Apr 22, 2017

• #5

FDibbins said:

Is that always the format your data is in - yyyy-mm-ddblahblah?
4 digit year, 2 digit month, 2 digit day (or is it day/month?)

Click to expand...

It is my understanding that that is the universal interpretation for that date format.

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Apr 22, 2017

• #6

Ford

I think it's some sort of standard date format, not sure which on though.

Have a shift here https://www.w3.org/TR/NOTE-datetime.

 

Last edited: Apr 22, 2017

Upvote 0

Sean15

Well-known Member

Joined

Jun 25, 2005

Messages

698

Office Version

1. 2010

Platform

1. Windows

• Apr 22, 2017

• #7

Thanks Norie and Rick Rothstein - your formula returns the required result.

FDibbins - data's dates are formatted as 2017-02-01T01:33:06Z.

Could someone tell me why didn't formula TEXT(B31,"mm/dd/yy") return the required date or text format of required date?

Regards,

Sean

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Apr 22, 2017

• #8

Sean

The value in B31 isn't a date value, it's text so you can't apply a date format like 'mm/dd/yy' to it.

 

Upvote 0

FDibbins

Well-known Member

Joined

Feb 16, 2013

Messages

6,723

• Apr 22, 2017

• #9

Rick and Norie, thanks for the feedback, I am not familiar with that specific "date" format, that's why I asked

 

Upvote 0

Sean15

Well-known Member

Joined

Jun 25, 2005

Messages

698

Office Version

1. 2010

Platform

1. Windows

• Apr 22, 2017

• #10

Norie said:

Sean

The value in B31 isn't a date value, it's text so you can't apply a date format like 'mm/dd/yy' to it.

Click to expand...

Thank you.

 

Upvote 0