Help

Update due date based on IF formula

Solved
Jump to Solution
2238 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!