Subtracting various time measurements from created time

[r_s]

Hi, I’m attempting to solve for the posted date/time of an item.

Desired result: date & time of post for a new item.

Available data: each item has some information about the time, but is listed in one of the following formats:

• x minute(s) ago
  -x day(s) ago
  -x week(s) ago

Success criteria: the best approximation of an items posted date/time understanding that “a week ago” could actually mean 7-13 days ago but, for my case, I can accept this as 7 days ago. The desired results will contain a date and time.

I’ve tried to use the “created time” field and find a way to subtract the time unit(s) I’m provided with, but to no avail.

Is there a way to accomplish this in Airtable?

Thank you in advance.

[Florian_Verdon2 · Accepted solution]

Hi @r_s,

Just to clarify, you already have a Single Line Text Field with “x unit ago” in your table that you would like to convert to a standard DateTime field ?

Two solutions here :

1. Would you be able to import an absolute date instead of a relative date ? Easiest and cleanest way
2. If not, then you should parse that string to get the value (number of units) and the unit.

With that information, you will be able to use the following formula to :
DATEADD({Original Date Here}, 10, 'days')

Florian

[r_s]

Hi Florian_Verdonck, thanks for the response. I was able to solve using the formula you provided : )

Was an oversight on my part, I saw that formula (DATEADD) but, I didn’t think to make the units a negative number.

DATEADD({Original Date Here}, -10, ‘days’)

Thanks for your help with this!