If date range includes a Friday - check a box

JSpears

I have a sheet with a start date and an end date. I would like a checkbox column to be checked if that date range includes a Friday.

Any ideas on how I would do that?

Thank you in advance.

Jennifer

Paul Newcome

Give this a try:

=IF(OR(WEEKDAY([Start Date]@row) = 6, WEEKDAY([End Date]@row) = 6, [End Date]@row > [Start Date]@row + (6 - WEEKDAY([Start Date]@row)) - IF(WEEKDAY([Start Date]@row) < 6, 7, 0) + 7), 1)

Eric Law

@JSpears I have a few ideas, none are elegant. Wondering what the purpose of that is, and if there is an easier way to help solve this.

JSpears

I have a user that needs to be notified if a training is going to take place on a Friday so that she can get front desk coverage. So the Start date is the start date of the training and the End date...well, you get it.

I'm open to any way to be able to get the information - does not have to be a checkbox.

Paul Newcome

Give this a try:

=IF(OR(WEEKDAY([Start Date]@row) = 6, WEEKDAY([End Date]@row) = 6, [End Date]@row > [Start Date]@row + (6 - WEEKDAY([Start Date]@row)) - IF(WEEKDAY([Start Date]@row) < 6, 7, 0) + 7), 1)

JSpears

@Paul Newcome - I'm in awe of your mind. Thank you.

Paul Newcome

Happy to help. 👍️

Honestly... I already had a formula tucked away to grab the "current Monday". A slight tweak to look at [Start Date] instead of TODAY(), another minor tweak to get Friday instead of Monday (change 2 numbers), and then add 7 days to get the following Friday.

Then we say if either the start or end date is a Friday or the End Date is past the start date's Friday then we must have a Friday in there somewhere.

I won't even go into the details of the overcomplicated messes I had tried first. Hahaha

CAH

Where the first instance of 'Days Not Worked' for a member of staff occurs (the first date of that person's annual leave), I want to input a distinct value (only once) in the column 'Deduct N/A Annual Leave'.

This is the sum of, where manager type is Company annual leave, the number of days in a date ranges between Start/Finish that that fall on any of the selected days in the multiple value dropdown column 'Days Not Worked'.

Please see screenshot of relevant columns. Any help gratefully received, many thanks in advance.