180 Day Lookback Formula

atlenp911

New Member
Joined
Jun 28, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Greetings all,
I need help creating a formula which alerts me if a specific employee has exceeded 90 working days in the past 180 calendar days from TODAY, OR if they will exceed 90 working days over 180 day rolling period in the future, since we have a tentative schedule created for the next 1 year. I attached a photo of how our schedule looks.

I believe that I need a formula that counts the incidence of an employees name in a rolling 180 day lookback period from every single date on the calendar. If they exceed 90 days in a 180 day lookback period from that specific date, I would like to make it so Excel notifies me in some way, such as highlighting that particular cell for that employee. Is that possible? Correct me if there's an easier way to evaluate this.

Example Question: Has Lopez exceeded his 90-in-180 day limit in the past, or will he exceed it in the future as he's scheduled currently?

Image Removed
 
Last edited by a moderator:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
The snip isn't very legible. Are you able to create a Mini Sheet?
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top