Add an expiry date

[Catherine_Robso]

Hope someone can help me with this. I would like to calculate a date which is 3 months from a date in my table. I’ve seen someone give a solution to a similar problem but it returns a text value and I need it to be a date.

Any suggestions?

[Emilio_Esteban]

Hi, Catherine. Maybe this function can help:
DATEADD([date], [#], ‘units’) Adds specified “count” units to a datetime.
DATEADD(Date, 10, ‘days’) => 10/9/2015 12:00am

[Julian_Kirkness]

Hi @Catherine_Robson

You are correct that at the moment Formula fields either result in Text values (as here) or numbers. There is a way to achieve this but it requires outside help - in this case Zapier could do what you need (as long as you don’t need the calculation to happen immediately. The only potential problem with this is that a Zapier will only run once for any given triggering record - so it wouldn’t update your result if you changed the initial date (unless you found a way to have another Zap do this).

I think you may need a paying subscription to Zapier to achieve this.

Hope this helps.

Julian

[Catherine_Robso]

Thanks! This works - the only thing is it returns it in the default Local/US format and I need it to be European. Do you happen to know if there a way to convert it (using another field if necessary?)

Edited to add: I have found out how to set the timezone, so this now works perfectly! Many thanks!

[Catherine_Robso]

Additional problem - I now want to work out how many days until the expiry date, but because the formula does not return a date format, I can’t use FROMNOW. I feel like DATETIME_PARSE might help but I can’t quite work out how to do it. Anyone have any ideas?

[Julian_Kirkness]

Hi @Catherine_Robson

I think you are overthinking this (understandably) - it turns out that the Datetime_Diff() function works with it’s parameters being formula fields displaying dates…

Field 6 is a formula - and here’s the result:

Hope this helps,

Julian

[Catherine_Robso]

You are right - I was totally overthinking it - easy to do once you go down the rabbit hole :slightly_smiling_face: Thanks, this works perfectly!

My next challenge is to create a formula that returns a value based on this date…I am almost certain I will be back for more help :joy: