Forum Discussion

jamess2150's avatar
jamess2150
Copper Contributor
Sep 13, 2024

How to convert Text to Date?

I've extracted a sheet like this with a webscrapper tool but it formates dates like this - is there a formula etc to make sure it formats it into a mm/yy format? Even if it says 3 years ago, wouldnt mind if it was current month but 3 years ago etc


Thanks

 

BI & Data Analysis
excel
office 365

2 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Sep 13, 2024

    jamess2150 

    As variant

    =LET(
  period, SUM( ISNUMBER( SEARCH({"week","month","year"}, A1 ) )*{0,-1,-12} ),
  amount, IFERROR(--LEFT(A1, FIND(" ", A1)-1 ), 1),
  IF( period = 0, TODAY()-7*amount, EDATE( TODAY(), amount*period) )
)
  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor
    Sep 13, 2024

    Try this formula: 

    =TEXT(
  IF(A2="a year ago", EDATE(TODAY(), -12),
  IF(A2="a month ago", EDATE(TODAY(), -1),
  IF(A2="a week ago", TODAY() - 7,
  IF(TEXTAFTER(A2, " ")="weeks ago", TODAY() - VALUE(TEXTBEFORE(A2, " ")) * 7,
  IF(TEXTAFTER(A2, " ")="months ago", EDATE(TODAY(), -VALUE(TEXTBEFORE(A2, " "))),
  IF(TEXTAFTER(A2, " ")="years ago", EDATE(TODAY(), -VALUE(TEXTBEFORE(A2, " ")) * 12), TODAY())))))), 
  "mm/yy")

     

Resources