How to combine an IF statement with Workday and MAX functions

Options
Yousef J.
Yousef J. ✭✭
in Formulas and Functions

Hi Friends,

I'm currently using the MAX function to determine the "Next Maintenance" date based on the "follow-up" columns as shown below. I'm looking for a way that if its output has a date of a weekend, it would automatically populate the next Monday date. As an example below, the next maintenance date coming from the MAX formula would be a weekend, and my goal is for it to directly have an output of August 23rd instead of 21st. Is it possible to add here an if statement with both Workday and MAX functions to serve the purpose or is there other ways of doing this?

Appreciate it!


image.png


· Share on FacebookShare on Twitter

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/22/21 Answer ✓
    Options

    Hi @Yousef J. 

    Hope you are fine, please try the following formula and convert it to column format formula ( If Saturday will add 2 days, if Sunday will add 1 day)

    =IFERROR(IF(WEEKDAY(MAX([Monthly - Follow-Up]@row:[Annual - Follow-Up]@row)) = 7,
MAX([Monthly - Follow-Up]@row:[Annual - Follow-Up]@row) + 2,
IF(WEEKDAY(MAX([Monthly - Follow-Up]@row:[Annual - Follow-Up]@row)) = 1,
MAX([Monthly - Follow-Up]@row:[Annual - Follow-Up]@row) + 1,
MAX([Monthly - Follow-Up]@row:[Annual - Follow-Up]@row))), "")

    the following screenshot shows the result:

    image.png


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/22/21 Answer ✓
    Options

    Hi @Yousef J. 

    Hope you are fine, please try the following formula and convert it to column format formula ( If Saturday will add 2 days, if Sunday will add 1 day)

    =IFERROR(IF(WEEKDAY(MAX([Monthly - Follow-Up]@row:[Annual - Follow-Up]@row)) = 7,
MAX([Monthly - Follow-Up]@row:[Annual - Follow-Up]@row) + 2,
IF(WEEKDAY(MAX([Monthly - Follow-Up]@row:[Annual - Follow-Up]@row)) = 1,
MAX([Monthly - Follow-Up]@row:[Annual - Follow-Up]@row) + 1,
MAX([Monthly - Follow-Up]@row:[Annual - Follow-Up]@row))), "")

    the following screenshot shows the result:

    image.png


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter
  • Yousef J.
    Yousef J. ✭✭
    Options

    It works! Thank you so much.

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Yousef J.

    Excellent, i will be happy to help you any time.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!