convert "a week ago" into an actual date-CodePudding

I have a google sheets document where a scraper gets reviews from google maps, I want to sort them by date but the only date the reviews contain are the human readable "XX days/weeks/months/years ago" so just sorting that column doesn't work.

the actual values I get:

a day ago
2 days ago
3 days ago
4 days ago
5 days ago
6 days ago
a week ago
2 weeks ago
3 weeks ago
a month ago
2 months ago
...
11 months ago
a year ago
2 years ago

Obviously I won't be able to get the actual date for reviews made more than 1 week ago, but an estimate would be good enough.

CodePudding user response：

Not sure if this is what you want:

=index(if(A1:A<>"",today()-(switch(iferror(regexextract(A1:A,"(day|week|month|year)"),),"day",1,"week",7,"month",365.25/12,"year",365.25,)*iferror(substitute(regexextract(A1:A,"([\da] )\ "),"a",1),)),))

Replace today() with the scraped date, which in the example, could be datevalue("2021-10-06").

CodePudding user response：

function weekago() {
  let d = new Date();
  d.setDate(d.getDate() - 7);
  Logger.log(d);
  return d;
}