Forum Discussion

nasirabd's avatar
nasirabd
Copper Contributor
Dec 13, 2021
Solved

Convert Date/Time String to Date Format

I am seeking assistance with converting a string that is a date/time value into a date format in Excel. I would like to use this field along with associated data in a pivot table to group by day, we...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 13, 2021

    nasirabd 

    You may convert text to date by formula like

    =DATE(
  RIGHT(A2,4),
  MONTH( 1&LEFT( RIGHT(A2,  LEN(A2) -4), 3) ),
  LEFT( RIGHT(A2,  LEN(A2) -8), 2))

    and apply to result any date format you wish.

jlambie61's avatar
jlambie61
Copper Contributor
Jan 14, 2022

SergeiBaklan 

I am struggling with a similar date time string as text but it looks like

DATE TIME
2021-09-15 12:15
2021-09-15 12:30
2021-09-15 12:45
2021-09-15 13:00
2021-09-15 13:15
2021-09-15 13:30
2021-09-15 13:45
2021-09-15 14:00

The conversion string you wrote returned 1/15/1900  So it translated one part of it I think...

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 14, 2022

jlambie61 

Are you sure you have texts, not actual datetime? You may check by entering =ISTEXT(A2) into empty cell.

  • jlambie61's avatar
    jlambie61
    Copper Contributor
    Jan 14, 2022
    I have it as a csv txt file and I cannot get this date time stamp string to come in as a number. I can get the data but the time comes as 1215 instead of 12:15
      • jlambie61's avatar
        jlambie61
        Copper Contributor
        Jan 19, 2022
        Apologies for the resend. I don't know the user interface here that let me get to your answer. The simpler version works. I'm curious to try the string formula.
        Thank you very much. Awkwardly dumb issue but with a lot of data, vital to have an easy way to convert.
  • jlambie61's avatar
    jlambie61
    Copper Contributor
    Jan 14, 2022
    It's text

Resources