SOLVED

Extracting Time from a Day-Month-Date-Time-Year value in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1881261%22%20slang%3D%22en-US%22%3EExtracting%20Time%20from%20a%20Day-Month-Date-Time-Year%20value%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1881261%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20with%20data%20like%20this%3A%20Wed%20Jan%2023%2023%3A58%3A50%202019.%3C%2FP%3E%3CP%3EHow%20do%20I%20extract%20only%20the%20time%3F%20Please%20help%20me%20on%20this.%20Most%20grateful!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1881261%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1881340%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20Time%20from%20a%20Day-Month-Date-Time-Year%20value%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1881340%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F866963%22%20target%3D%22_blank%22%3E%40AngelikiEfth%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20you%20have%20a%20date%2Btime%20value%20in%20cell%20A2.%3C%2FP%3E%0A%3CP%3EIn%20B2%2C%20there%20the%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTIMEVALUE(MID(SUBSTITUTE(G8%2C%22%20%22%2CREPT(%22%20%22%2C255))%2C640%2C255))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20format%20it%20as%20time%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1881364%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20Time%20from%20a%20Day-Month-Date-Time-Year%20value%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1881364%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F866963%22%20target%3D%22_blank%22%3E%40AngelikiEfth%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20a%20variant%2C%20that%20could%20be%3A%3C%2FP%3E%3CPRE%3E%3DTIMEVALUE(MID(A1%2CFIND(%22%3A%22%2CA1)-2%2C8))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1881369%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20Time%20from%20a%20Day-Month-Date-Time-Year%20value%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1881369%22%20slang%3D%22en-US%22%3E%3CP%3E%3CEM%3EED%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EIgnore.%20I%20was%20sleeping.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3E%2FED%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F866963%22%20target%3D%22_blank%22%3E%40AngelikiEfth%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20the%20data%20%22real%22%20excel%20data%2Ftime%20or%20a%20textstring%3F%3C%2FP%3E%3CP%3EIf%20real%20excel-time%3C%2FP%3E%3CP%3E%3DMOD(A1%2C1)%3C%2FP%3E%3CP%3EAnd%20change%20the%20number-viewing%20format%20to%20something%20appropriate%20(ctrl%2B3)%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20it%20is%20a%20textstring.%20extrakt%20text%20from%20letter%2012%20and%208%20forward%3C%2FP%3E%3CP%3E%3DMID(A1%2C12%2C8)%3C%2FP%3E%3CP%3EAnd%20if%20you%20want%20to%20be%20able%20to%20calculate.%20Transform%20to%20proper%20Time-data%3C%2FP%3E%3CP%3E%3DTIMEVALUE((MID(A1%2C12%2C8)))%3C%2FP%3E%3CP%3E(ctrl%2B3)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1881546%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20Time%20from%20a%20Day-Month-Date-Time-Year%20value%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1881546%22%20slang%3D%22en-US%22%3EThank%20you%20lots!!%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I have a table with data like this: Wed Jan 23 23:58:50 2019.

How do I extract only the time? Please help me on this. Most grateful!!!

6 Replies
Highlighted

@AngelikiEfth 

Let's say you have a date+time value in cell A2.

In B2, there the formula

=TIMEVALUE(MID(SUBSTITUTE(G8," ",REPT(" ",255)),640,255))

and format it as time

Highlighted
Best Response confirmed by AngelikiEfth (Occasional Contributor)
Solution

Hello @AngelikiEfth,

 

As a variant, that could be:

=TIMEVALUE(MID(A1,FIND(":",A1)-2,8))

 

Highlighted

ED

Ignore. I was sleeping.

/ED

@AngelikiEfth 

Is the data "real" excel data/time or a textstring?

If real excel-time

=MOD(A1,1)

And change the number-viewing format to something appropriate (ctrl+3) 

If it is a textstring. extrakt text from letter 12 and 8 forward

=MID(A1,12,8)

And if you want to be able to calculate. Transform to proper Time-data

=TIMEVALUE((MID(A1,12,8)))

(ctrl+3)

 

 

Highlighted
Highlighted
Highlighted