Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Update due date based on IF formula

Solved
Jump to Solution
2544 5
cancel
Showing results for 
Search instead for 
Did you mean: 
HaraldPalma
5 - Automation Enthusiast
5 - Automation Enthusiast

I am new here, so please forgive me if I ask a question that might have been asked before.
I keep track of my software subscriptions with Airtable, some are monthly based and some are yearly. Therefore, I created a single selection field with two options (monthly, yearly) and a date field that shows when the subscriptions are due. Now I would like to automatically update the due date after it has passed under the condition of monthly or yearly. 

I had something in mind like: IF ({subscription} = "yearly", NOW(), DATEADD( *1, 'year')({subscription} = "monthly", NOW(), DATEADD( *1, 'month')

I guess it's terribly wrong, since it never worked 😭

 

3 Solutions

Accepted Solutions
Ilan_Ben_Yaakov
6 - Interface Innovator
6 - Interface Innovator

hi,

It should look like 

IF(subscription="yearly",DATEADD(NOW(),1,"year"),DATEADD(NOW(),1,"month"))

See Solution in Thread

I assumed there could be only two options. 

you can also use the SWITCH formula like this

SWITCH(
  subscription,
    'yearly',DATEADD(NOW(),1,'year'),
    'monthly',DATEADD(NOW(),1,'month')
)

See Solution in Thread

@Ilan_Ben_Yaakov , @HaraldPalma I think there should be another correction. NOW() function will update the value of the field each day and you are going to have never-ending subscription, which is maybe good, but out of reality )). So I suggest to have a date field where you would store the starting date of a subscription, and this field you address in you formula.

SWITCH(
  subscription,
    'yearly',DATEADD({start of subscrition},1,'year'),
    'monthly',DATEADD({start of subscrition},1,'month')
)

See Solution in Thread

5 Replies 5
Ilan_Ben_Yaakov
6 - Interface Innovator
6 - Interface Innovator

hi,

It should look like 

IF(subscription="yearly",DATEADD(NOW(),1,"year"),DATEADD(NOW(),1,"month"))

I assumed there could be only two options. 

you can also use the SWITCH formula like this

SWITCH(
  subscription,
    'yearly',DATEADD(NOW(),1,'year'),
    'monthly',DATEADD(NOW(),1,'month')
)

@Ilan_Ben_Yaakov , @HaraldPalma I think there should be another correction. NOW() function will update the value of the field each day and you are going to have never-ending subscription, which is maybe good, but out of reality )). So I suggest to have a date field where you would store the starting date of a subscription, and this field you address in you formula.

SWITCH(
  subscription,
    'yearly',DATEADD({start of subscrition},1,'year'),
    'monthly',DATEADD({start of subscrition},1,'month')
)

Thank you very much! Such a nice welcoming in this forum, and two excellent solutions 🙂 I didn't think of the SWITCH - great idea. I guess it's either or if both ways work. 

HaraldPalma
5 - Automation Enthusiast
5 - Automation Enthusiast

- that was right 🙂 - this was the solution I used - thank you!