if blank & weekday formulas

Kathy Morgan

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.

Mike Wilday

Do you want anything if the task is marked complete?

Mike Wilday

Try this one.

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

Mike Wilday

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

Mike Wilday

Do you want anything if the task is marked complete?

Kathy Morgan

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

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

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

=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

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

Thank you. That worked.

I appreciate your help.

Kathy Morgan

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

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

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

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

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

ah. okay.