if blank & weekday formulas

Options

I want to raise a flag to warn me 3 working days prior to my expiration date.

I dont want a flag to raise if my columns are blank.

Best Answers

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓
    Options

    Do you want anything if the task is marked complete?

  • Kathy Morgan
    edited 12/14/20
    Options

    Yes, I want to raise the flag 3 days prior to expiration date and "Done" is not check. In addtion, dont raise flag if my expiration date is blank

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Try this one. You'll need to replace "Expiration Date Column Name" with the actual name of your expiration date row. Remove the brackets [ ] if your column name is one word and doesn't end in a number.

    =IF(AND(Done@row <> 1, [Expiration Date Column Name]@row >=Today(-3)),1, 0)

  • Kathy Morgan
    Options

    This is not working. If you notice the 20th is raised. If its working days 3 days (no weekends or holidays) before the expiration date raise the flag, but if the Tix exp. date is blank keep flag lowered. When the done box is marked complete lower the flag.

    This is the formula used. =IF(AND(Done@row <> 1, [Ticket Expiration Date]@row >= TODAY(-3)), 1, 0)


  • Kathy Morgan
    Options

    =IF(ISBLANK([Ticket Expiration Date]@row), 0, IF(AND([Ticket Expiration Date]@row <= TODAY(3)), 1, 0))

    This formula is working, however I can not lower the flag if it is marked done.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this one.

    IF(OR(ISBLANK([Ticket Expiration Date]@row), Done@row = 1), 0, IF(AND([Ticket Expiration Date]@row <= TODAY(3)), 1, 0))

  • Kathy Morgan
    Options

    Thank you. That worked.

    I appreciate your help.

  • Kathy Morgan
    Options

    I got so excited that the formula worked, I forgot that I needed it to calculate work days.

    The other problem I am having is when writing the formula to calculate 3 days after a date the flag wont raise.

    =IF(OR(ISBLANK([Locates Called In]@row), Cleared@row = 1), 0, IF(AND([Locates Called In]@row >= TODAY(3)), 1, 0))

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    edited 12/16/20
    Options

    Three days after which date? I don't understand. Is there another date you want to raise the flag by?

    =IF(OR(ISBLANK([Locates Called In]@row), Cleared@row = 1), 0, IF([Locates Called In]@row >= Workday(TODAY(3),0), 1, 0))

    This one is based on a workday.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓
    Options

    I had to modify it slightly - try out this modified formula.

    =IF(OR(ISBLANK([Locates Called In]@row), Cleared@row = 1), 0, IF([Locates Called In]@row >= Workday(TODAY(3),0), 1, 0))

  • Kathy Morgan
    Options

    I am sorry Mike. I should have been more clear.

    three days after the Locates called in date.

    I have to verify if all companies have marked their locates. That is the 3 day mark. But the days are business days and not calendar days. so if the dates are a Friday then the clock doesnt start until Monday. Does that help?

  • Kathy Morgan
    Options

    At first it didnt work.

    Your formula:

    =IF(OR(ISBLANK([Locates Called In]@row), Cleared@row = 1), 0, IF([Locates Called In]@row >= Workday(TODAY(3),0), 1, 0))

    I changed ">" to "<" and it works.

    Thank you for your help.


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!