Jan 05, 2017 05:48 AM
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?
Jan 06, 2017 03:51 PM
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
Jan 08, 2017 03:02 AM
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
Jan 09, 2017 08:29 AM
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!
Jan 10, 2017 04:10 AM
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?
Jan 10, 2017 07:09 AM
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
Jan 10, 2017 07:20 AM
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: