I have a list of employees, the Item they booked time against and the forecasted time.
Now when there is a bank holiday I have to amend their forecast and remove the allocated bank holiday time, I thought that would be simple but can't get it right.
Here is an example:
Data...
Name
Item
Forecast
Date
John Doe
Scone
5
31/05/2021
John Doe
Tea
5
31/05/2021
John Doe
Cake
15
31/05/2021
John Doe
Learning
5
31/05/2021
John Doe
Bank Holiday
7
31/05/2021
John Doe
Coffee
5
31/05/2021
John Doe
Scone
10
07/06/2021
John Doe
Tea
10
07/06/2021
John Doe
Cake
15
07/06/2021
31/05/2021
07/06/2021
Name
Item
Forecast
Forecast
John Doe
Scone
5
10
John Doe
Tea
5
10
John Doe
Cake
15
15
John Doe
Learning
5
0
John Doe
Bank Holiday
7
0
John Doe
Coffee
5
0
Total
42
35
The issue I have is that the forecast includes the bank holiday and the total forecast for John Doe should be 35 for that week not 42, so I was hoping I could create an expression that would take the largest one for 31/05/2021 which is Cake and subtract the bank holiday from there so Cake would be 8 and the total would be 35.
It's a large list with various employees and their items vary but the bank holiday always shows like that.
