Forum Discussion

AngelikiEfth's avatar
AngelikiEfth
Copper Contributor
Nov 12, 2020
Solved

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

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!!!

excel
office 365

6 Replies

  • MindreVetande's avatar
    MindreVetande
    Iron Contributor
    Nov 12, 2020

    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)

     

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 12, 2020

    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

Resources