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, wo...
BI & Data Analysis
excel
office 365
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