Convert text numbers to date

jlugo

Board Regular
Joined
Aug 12, 2011
Messages
146
Hi

My sample text (which is general format when report generates) is this:

20141205 (December 5, 2014)

How can I convert that to a date so that I may drag a formula down to count # of days?
I tried dateif and datevalue and didn't work.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Hi.

Assuming the first part of the string is always of the precise format yymmddd followed by a space:

=0+REPLACE(REPLACE(LEFT(A1,FIND(" ",A1)),5,,"/"),8,,"/")

Edit: seeing VoG's solution it appears I may have misunderstood. I presumed that the part in parentheses was also in the cell entry, though perhaps this was just your attempt at clarification?

Regards
 

jlugo

Board Regular
Joined
Aug 12, 2011
Messages
146
YOU ARE AWESOME!!! Thanks so much.

May the universe give you abundant blessings for what you do on here!!!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,914
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top