Skip to main content

Hi all,

Using Make, Airtable and an API, I built an automation that scrapes reviews of a cultural event from Google Maps. On Google, the date on which a user left a review is not given. Instead, Google provides us with a time period that matches the amount of time that elapsed since the user left his/her review (e.g. 'one year ago'). This type of data does not make it easy to sort reviews in a chronological order. Consequently, I went about transforming this type of data into dates.

For this data conversion, I created intermediary fields – Time Unit Count, Time Unit Type and Search Date – that I then combine in a formula in the Date field. But I'm unsatisfied with 2 of those intermediary fields' formulas.

1. Time Unit Type field: for this field, I used a series of nested IF statements because I didn't manage to use the SWITCH function properly in this use case. This gives me a long and not-very-clear formula.

Would any of you guys have a more elegant solution to suggest?

2. Time Unit Count field: as for this field, I simply didn't manage to use the SWITCH function so that I could get a formula along the lines of "if {Date (Raw)} contains 'day' or 'days', then extract what lies nefore; if {Date (Raw)} contains 'week' or 'weeks', then extract what lies before; etc. ".

Do you guys know how to solve this?



I would appreciate your help on this.

Here are simpler formulas.

Time Unit Type. Your formula works, but is wordier than necessary. You do not need to search for the plural forms (days, weeks, months, years) because they include the singular forms. You also do not need to compare the result of the OR() function with 1.

A totally different, more concise method is to use RegEx and return whichever unit you find.

 

 

 

Time Unit Count. Your formula has two issues.

(1) Your formula does not recognize that when the text begins with the word "a", the value should be 1. This can be done by checking the left most character to decide if the result should be the implied 1.

(2) Notice that your formula result is left aligned. This means that your result is actually text and not a number. Sometimes Airtable converts text to a number for you, but it is good practice to make data conversions like this explicit. This can be done by using the VALUE() function.

 

Here is the final result of these formulas.

 

Here are some other considerations.

- Will the {Date (Raw)} field ever have mixed units, such as "2 years 3 months"?

- Will the {Date (Raw)} field every have a decimal, such as "1.5 years ago"?

- Will the {Date (Raw)} field ever be blank?

- Will the {Date (Raw)} field ever have an interval of less than a day, such as hours, minutes, seconds, or a "zero" value?

 

 

Hi @kuovonne,

1. Your formula with REGEX_EXTRACT() looks a lot better! Short and effective. Thanks for showing me how to use it.

2. Again your suggested formula is much more elegant than mine. Thanks also for sharing the best practice of using the VALUE() function for number-type fields.

The answer to all your other considerations is no.

I'm amazed at how reactive you were and how accurate your answer is!

Reply


Terms & Conditions