DATES FORMULA

Filippo01

I'm trying to create a formula for the below information:

find if in a range of start dates and end dates of a specific job, one or more of these dates correspond to the current week date

if that's happen the guy is not available otherwise is available for this week

thanks

Mike TV

@Filippo01

First we have to define the date range for this week. I've done that by first figuring out which day of the week it is.

Day of Week formula:

=WEEKDAY(TODAY())

Start of Week formula:

=TODAY(-[Day of Week]@row) + 1

End of Week formula:

=TODAY(-[Day of Week]@row) + 7

Finally, we can write a formula to checkmark a box if the Start Date or End Date falls within the Start of Week or End of Week days:

=IF(OR(AND([Start Date]@row >= [Start of Week]@row, [Start Date]@row <= [End of Week]@row), AND([End Date]@row >= [Start of Week]@row, [End Date]@row <= [End of Week]@row)), 1, IF(AND([Start Date]@row < [Start of Week]@row, [End Date]@row > [End of Week]@row), 1))

Filippo01

thanks for helping @Mike TV

unfortunately i can not have a column representing the current week, indeed i was asking for a formula which contain the current week, like for example weeknumber(TODAY())

is possible to use this formula you just sent me but using weeknumber(TODAY()) instead pf the current week's column??

Mike TV

@Filippo01

Unfortunately, I do not know of a way to do this with zero helper columns. Maybe someone else can guide you down that direction. I'm not sure that's possible. You know you can hide your helper columns so they're not displayed, right?

Filippo01

@Mike TV

no actually i did not know that, but is the formula going to update every week by itself in this way?

because i need a formula able to update the weekly availability of the guys

Mike TV

@Filippo01

Yes it will update automatically because it is using the TODAY() function a lot.