if blank & weekday formulas
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

Do you want anything if the task is marked complete?

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

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

Do you want anything if the task is marked complete?

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

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)

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)

=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.

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

Thank you. That worked.
I appreciate your help.

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

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.

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

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?

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.

ah. okay.
Help Article Resources
Categories
Check out the Formula Handbook template!