Forum Discussion

Anthony_Luk's avatar
Anthony_Luk
Copper Contributor
May 04, 2022
Solved

How can I extract or convert text data into date?

Hi there,

 

The data and time data as below is written as text. I'm unable to convert it into date format in order to process further analysis such as =year or = month.

Kindly please advise.

Many thanks

 

excel
  • mathetes's avatar
    mathetes
    May 04, 2022
    Assuming that first row is in cell P2, use =DATEVALUE(P2) You could then go further with =YEAR(DATEVALUE(P2), etc.

3 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    May 04, 2022
    Try this:
    =LET(p,P2,LEFT(p,FIND(" -",p))*1)

    Then format cell with custom code: yyy-mm-dd hh:mm:ss
  • mathetes's avatar
    mathetes
    Silver Contributor
    May 04, 2022
    Assuming that first row is in cell P2, use =DATEVALUE(P2) You could then go further with =YEAR(DATEVALUE(P2), etc.

Resources