Skip to main content
Solved

Update due date based on IF formula

HaraldPalma
Forum|alt.badge.img+4

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 😭

 

Best answer by Ilan_Ben_Yaakov

hi,

It should look like 

IF(subscription="yearly",DATEADD(NOW(),1,"year"),DATEADD(NOW(),1,"month"))
View original
    Did this topic help you find an answer to your question?

    5 replies

    I
    Forum|alt.badge.img+7
    • Ilan_Ben_Yaakov
    • Participating Frequently
    • 21 replies
    • Answer
    • December 18, 2022

    hi,

    It should look like 

    IF(subscription="yearly",DATEADD(NOW(),1,"year"),DATEADD(NOW(),1,"month"))
    HaraldPalma
    1 person likes this
    • HaraldPalma
      HaraldPalma
    I
    Forum|alt.badge.img+7
    • Ilan_Ben_Yaakov
    • Participating Frequently
    • 21 replies
    • December 18, 2022
    Ilan_Ben_Yaakov wrote:

    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') )
    HaraldPalma
    1 person likes this
    • HaraldPalma
      HaraldPalma
    A
    Forum|alt.badge.img+18
    Ilan_Ben_Yaakov wrote:

    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') )
    I
    HaraldPalma
    2 people like this
    • I
      Ilan_Ben_Yaakov
    • HaraldPalma
      HaraldPalma
    HaraldPalma
    Forum|alt.badge.img+4
    • HaraldPalma
    • Author
    • Participating Frequently
    • 6 replies
    • December 18, 2022
    Ilan_Ben_Yaakov wrote:

    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') )

    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. 

    kuovonne
    I
    2 people like this
    • kuovonne
      kuovonne
    • I
      Ilan_Ben_Yaakov
    HaraldPalma
    Forum|alt.badge.img+4
    • HaraldPalma
    • Author
    • Participating Frequently
    • 6 replies
    • December 18, 2022

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

    I
    1 person likes this
    • I
      Ilan_Ben_Yaakov

    Reply


    Most helpful members this week

    Terms & Conditions