Help with Formula

tsammons

Could someone please help me with a formula?

Basically I want a formula to return a "Yes" if any data in the Area column = Location 1, but between a specified date range. If Location 1 is not in the area column, then return a "No". Thank you.

Jeff M.

I came up with this formula and it worked for me when I tested it out. Go ahead and give it a shot and let me know if it doesn't work for you.

=IF(AND(HAS(Area@row:Area@row, "Location 1"), NETDAYS([Start Date]@row, [End Date]@row) > 0) = true, "Yes", "NO")

Jeff M.

I came up with this formula and it worked for me when I tested it out. Go ahead and give it a shot and let me know if it doesn't work for you.

=IF(AND(HAS(Area@row:Area@row, "Location 1"), NETDAYS([Start Date]@row, [End Date]@row) > 0) = true, "Yes", "NO")

Jeff M.

If your sheet has only one date column, then you could do something like this:

=IF(AND(HAS(Area@row:Area@row, "Location 1"), [Date]@row >= TODAY(-7)) = true, "Yes", "NO")

This will check to see if the date is within the last 7 days.

OR convert the date to a 1-52 weeknumber to determine if the date entered is greater or less than that week.

=IF(AND(HAS(Area@row:Area@row, "Location 1"), WEEKNUMBER([Date]@row) < 40) = true, "Yes", "NO"

tsammons

Thanks Jeff M. That worked!