Mod function substitute

I am creating a due date field that will auto update for a Dashboard.  The goal is that every 2 weeks an updated date will be shown.   From what I can see MOD is not supported in Smartsheets.

I am wondering if someone is able to help me with a mod substitute on a date field like I have done in Excel.  The formula in Excel is as follows:

=MAX(IF(MOD((A2-B2),14)=0,A2+14,A2-MOD((A2-B2),14)+14),B2)

I also attached a JPEG called "MOD Date.jpeg" of my Excel document. 

Thanks for any help you can give.

Cameron

 

 

MOD Date.jpeg

Tags:

Comments

  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Hi Cameron,

    I'm uncertain why you are using MOD in your Excel formula. Based on what is written in C1, a simple IF statement would do the same job.

    If C1 is what you're looking to achieve in Smartsheet, this should do the trick:

    =IF(Today1 + 14 > Manual1, Today1 + 14, Manual1)

    If you're only wanting to deal with working days:

    =IF(WORKDAY(Today1, 9) > Manual1, WORKDAY(Today1, 9), Manual1)

    Kind regards,

    Chris McKay

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!