Checking Mutual Dates for Annual Holiday Planning

G

gunars

New Member
Joined
May 17, 2024
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone,

We've just planned my team's annual holidays.

I need to check if there are mutual dates among those days to prevent absence of more than two people at the same time in the office.

Names and the dates must have been checked by the formula and show the names if someone is on holiday or not in a specific date.
However I can't find a way that works for it.

Table design is flexible and I can change it with a suitable one.

Any help will be highly appreciated.

Thanks.

Dates:

Book1
CDEFGHIJKLMN
4First DayReturn DayWork Day CountDay 1Day 2Day 3Day 4Day 5Day 6Day 7
5Staff 124.06.202401.07.2024524.06.202425.06.202426.06.202427.06.202428.06.202429.06.202430.06.2024
6Staff 105.08.202412.08.2024505.08.202406.08.202407.08.202408.08.202409.08.202410.08.202411.08.2024
7Staff 114.10.202421.10.2024514.10.202415.10.202416.10.202417.10.202418.10.202419.10.202420.10.2024
8Staff 216.09.202423.09.2024516.09.202417.09.202418.09.202419.09.202420.09.202421.09.202422.09.2024
9Staff 221.10.202428.10.2024521.10.202422.10.202423.10.202424.10.202425.10.202426.10.202427.10.2024
10Staff 203.06.202410.06.2024503.06.202404.06.202405.06.202406.06.202407.06.202408.06.202409.06.2024
11Staff 305.08.202412.08.2024505.08.202406.08.202407.08.202408.08.202409.08.202410.08.202411.08.2024
12Staff 328.10.202404.11.2024528.10.202429.10.202430.10.202431.10.202401.11.202402.11.202403.11.2024
13Staff 318.11.202425.11.2024518.11.202419.11.202420.11.202421.11.202422.11.202423.11.202424.11.2024
14Staff 412.08.202419.08.2024512.08.202413.08.202414.08.202415.08.202416.08.202417.08.202418.08.2024
15Staff 407.10.202414.10.2024507.10.202408.10.202409.10.202410.10.202411.10.202412.10.202413.10.2024
16Staff 502.09.202409.09.2024502.09.202403.09.202404.09.202405.09.202406.09.202407.09.202408.09.2024
17Staff 507.10.202414.10.2024507.10.202408.10.202409.10.202410.10.202411.10.202412.10.202413.10.2024
18Staff 504.11.202411.11.2024504.11.202405.11.202406.11.202407.11.202408.11.202409.11.202410.11.2024
19Staff 608.07.202415.07.2024508.07.202409.07.202410.07.202411.07.202412.07.202413.07.202414.07.2024
20Staff 612.08.202419.08.2024512.08.202413.08.202414.08.202415.08.202416.08.202417.08.202418.08.2024
21Staff 624.06.202401.07.2024524.06.202425.06.202426.06.202427.06.202428.06.202429.06.202430.06.2024
22Staff 708.07.202415.07.2024508.07.202409.07.202410.07.202411.07.202412.07.202413.07.202414.07.2024
23Staff 712.08.202419.08.2024512.08.202413.08.202414.08.202415.08.202416.08.202417.08.202418.08.2024
24Staff 716.09.202423.09.2024516.09.202417.09.202418.09.202419.09.202420.09.202421.09.202422.09.2024
25Staff 805.08.202412.08.2024505.08.202406.08.202407.08.202408.08.202409.08.202410.08.202411.08.2024
26Staff 807.10.202414.10.2024507.10.202408.10.202409.10.202410.10.202411.10.202412.10.202413.10.2024
27Staff 804.11.202411.11.2024504.11.202405.11.202406.11.202407.11.202408.11.202409.11.202410.11.2024
28Staff 909.09.202416.09.2024509.09.202410.09.202411.09.202412.09.202413.09.202414.09.202415.09.2024
29Staff 922.07.202429.07.2024522.07.202423.07.202424.07.202425.07.202426.07.202427.07.202428.07.2024
30Staff 926.08.202402.09.2024526.08.202427.08.202428.08.202429.08.202430.08.202431.08.202401.09.2024
Sheet1
Cell Formulas
RangeFormula
H5:H30H5=+D5
I5:N30I5=+H5+1
D8D8=+D7-28
D9,D30,D23:D24D9=+D8+35
D10D10=+D9-140
D27D27=+D26+28
E5:E30E5=+D5+7



Days:

Book1
QRSTUVWXYZAAAB
403.06.202404.06.202405.06.202406.06.202407.06.202408.06.202409.06.202410.06.202411.06.202412.06.202413.06.202414.06.2024
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Sheet1
Cell Formulas
RangeFormula
R4:AB4R4=+Q4+1
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Book1.xlsm
ABCDEFGHIJKLMN
1Total Staff on leaveStaff 1Staff 2Staff 3Staff 4Staff 5Staff 1Staff 2Staff 3Staff 4Staff 5
203-06-2024011111Start24-06-202416-09-2024
304-06-2024011111End01-07-202423-09-2024
405-06-2024011111Days55000
506-06-2024011111Start05-08-202421-06-2024
607-06-2024011111End12-08-202428-06-2024
708-06-2024011111Days55000
809-06-2024011111Start14-10-2024
910-06-2024011111End21-10-2024
1011-06-2024011111Days50000
1112-06-2024011111Start
1213-06-2024011111End
1314-06-2024011111Days00000
1415-06-2024011111
1516-06-2024011111
1617-06-2024011111
1718-06-2024011111
1819-06-2024011111
1920-06-2024011111
2021-06-2024010111
2122-06-2024010111
2223-06-2024010111
2324-06-2024000111
2425-06-2024000111
2526-06-2024000111
2627-06-2024000111
2728-06-2024000111
2829-06-2024001111
2930-06-2024001111
Sheet6
Cell Formulas
RangeFormula
B2:B29B2=SUMIF(C2:G2,0)
C2:G29C2=IF(OR(AND($A2>=J$2,$A2<=J$3),AND($A2>=J$5,$A2<=J$6),AND($A2>=J$8,$A2<=J$9),AND($A2>=J$11,$A2<=J$12),AND($A2>=J$14,$A2<=J$15),AND($A2>=J$17,$A2<=J$18)),0,1)
K2K2=J8-28
A3:A29A3=A2+1
J3:K3,K6J3=J2+7
J4:N4,J13:N13,J10:N10,J7:N7J4=IFERROR(J3-J2-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(J2&":"&J3)))=7))-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(J2&":"&J3)))=1)),0)
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Macro for comparing dates and giving output as mentioned
2
Replies
13
Views
2K
venkat1926
V

Forum statistics

Threads
1,223,884
Messages
6,175,177
Members
452,615
Latest member
bogeys2birdies

Share this page

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