Hello!
I would need help learning how to convert text to date, see below.
I get the following text from IFTTT to an excel sheet.
January 19, 2019 at 11:24PM
How can I convert this to a date / time format?
Hello!
I would need help learning how to convert text to date, see below.
I get the following text from IFTTT to an excel sheet.
January 19, 2019 at 11:24PM
How can I convert this to a date / time format?
Hello my friend.
Such a simple question.
Excel has a couple of simple tools that normally do the conversion.
But they don't work for your data.
So you are going to have to build a formula to do it.
Datevalue() will normally convert a date as text into a date that Excel understands. Unfortunately Excel only understands, 1, 01 or Jan not January.
So we have to get your date into a format that Datevalue undertands ie 19/1/2019
If A1 is: January 19, 2019 at 11:24PM
Then: MID(A1,FIND(" ",A1)+1,2) = 19
LEFT(A1,FIND(" ",A1)-1) = January
So:
MATCH( LEFT(A1,FIND(" ",A1)-1),{"January","February"," March","April","May","June","July","August","September","October","November","December"},0)
Returns =1
MID(A1,FIND(",",A1)+2,4) = 2019
Put that all together with a couple of "/" then we get
=MID(A1,FIND(" ",A1)+1,2)&"/"&MATCH( LEFT(A1,FIND(" ",A1)-1),{"January","February"," March","April","May","June","July","August","September","October","November","December"},0)&"/"& MID(A1,FIND(",",A1)+2,4)
This returns 19/1/2019
Put that into Datevalue returns = 43484 which is your date as Excel sees it.
=Datevalue(MID(A1,FIND(" ",A1)+1,2)&"/"&MATCH( LEFT(A1,FIND(" ",A1)-1),{"January","February"," March","April","May","June","July","August","September","October","November","December"},0)&"/"& MID(A1,FIND(",",A1)+2,4))
'********************************************************************************************************************
With regards to the time.
RIGHT(A1,7) = 11:24PM
So LEFT(RIGHT(A1,7),5) = 11:24
Using Timevalue(LEFT(RIGHT(A1,7),5)) returns 0.475 which is 11:24AM as Excel sees it.
Obviously you need 11:24 PM so yo need to add 0.5 which is 12/24 ie 12 hours
+IF(RIGHT(A1,2)="PM",0.5,0)
So: Timevalue(LEFT(RIGHT(A1,7),5))+IF(RIGHT(A1,2)="PM",0.5,0) = 0.975 which is 23:24 as Excel sees it.
'************************************************************************************************************************
So you formula is:
=DATEVALUE(MID(A1,FIND(" ",A1)+1,2)&"/"&MATCH( LEFT(A1,FIND(" ",A1)-1),{"January","February"," March","April","May","June","July","August","September","October","November","December"},0)&"/"& MID(A1,FIND(",",A1)+2,4))+TIMEVALUE(LEFT(RIGHT(A1,7),5))+IF(RIGHT(A1,2)="PM",0.5,0)
Which returns: 43484.975. There you are all done.
Sorry. You are saying "But That Does Not Look Like A Date!!!!"
Ok so you now need to format your cell so Excel knows to Display your number as Date and Time.
So Right Click on your cell
Select Format Cell
Select Custom
And paste this where it says Type: dd/mm/yyyy hh:mm then click on Ok.
Phewwwwwwww.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
A slightly shorter version...
Assuming this text is in K12...
Date...
=DATEVALUE(LEFT(K12,FIND(" at",K12)-1))
Time...
=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(RIGHT(K12,7),"PM"," PM"),"AM"," AM"))
Then just add them together...
=DATEVALUE(LEFT(K12,FIND(" at",K12)-1))+TIMEVALUE(SUBSTITUTE(SUBSTITUTE(RIGHT(K12,7),"PM"," PM"),"AM"," AM"))
Then format as above
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks