Forum Discussion

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

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.
text to date.xlsx10 KB

Diamond Contributor

Dec 13, 2021

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.

text to date.xlsx10 KB

jlambie61
Copper Contributor
Jan 19, 2022
SergeiBaklan

Still looking for help with this text to date data conversion
MDB_293 (7).xlsx355 KB
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
  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
    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
nasirabd
Copper Contributor
Dec 13, 2021
Thank you for the quick response Sergei. The formula you provided works perfectly!
- SergeiBaklan
  Diamond Contributor
  Dec 13, 2021
  nasirabd , you are welcome, glad it helped

Resources