Timesheet vs travel dates

[alessiaexcels]

Hi there,

I am trying to cross check that timesheet booking dates aligns with when people travel if they are fly in, fly out (FIFO) employees. I have a timesheet system report for all people on the project (includes FIFO and non-FIFO staff) and some travel data which has ID, fly in date and fly out date. I want to populate column D in the ’Timesheet report’ tab with either a Y or N if there person was onsite during that period based on the dates in the 'Travel data’ tab. So any day equal to or in between the fly in and fly out date for that person, I want to show a Y for those dates and an N for any date outside of that. I hope that makes sense.

Thank you in advance.

Kind regards
Alessia

Timesheet vs travel data.xlsx
	A	B	C	D
1	Person ID	Date	Hours Booked	Onsite? (Y or N)
2	100072	6/01/2025	8
3	100072	7/01/2025	8
4	100072	9/01/2025	8
5	100072	14/01/2025	8
6	100072	15/01/2025	8
7	100072	16/01/2025	8
8	100072	20/01/2025	8
9	100072	21/01/2025	8
10	100072	22/01/2025	8
11	100072	23/01/2025	8
12	100072	29/01/2025	8
13	100072	30/01/2025	8
14	100080	6/01/2025	10
15	100080	7/01/2025	10
16	100080	8/01/2025	10
17	100080	9/01/2025	10
18	100080	10/01/2025	10
19	100080	11/01/2025	10
20	100080	13/01/2025	10
21	100080	14/01/2025	10
22	100080	15/01/2025	10
23	100080	16/01/2025	10
24	100080	17/01/2025	10
25	100080	18/01/2025	10
26	100080	19/01/2025	10
27	100080	20/01/2025	10
28	100080	21/01/2025	10
29	100080	22/01/2025	10
30	100080	23/01/2025	10
31	100080	30/01/2025	10
32	100080	31/01/2025	10
33	100081	6/01/2025	10
34	100081	7/01/2025	10
35	100081	8/01/2025	10
36	100081	9/01/2025	10
37	100081	10/01/2025	10
38	100081	11/01/2025	10
39	100081	13/01/2025	8
40	100081	14/01/2025	8
41	100081	15/01/2025	8
42	100081	16/01/2025	8
43	100081	17/01/2025	8
44	100081	27/01/2025	10
45	100081	28/01/2025	10
46	100081	29/01/2025	10
47	100081	30/01/2025	10
48	100081	31/01/2025	10
49	100089	6/01/2025	10
50	100089	7/01/2025	10
51	100089	8/01/2025	10
52	100089	9/01/2025	10
53	100089	10/01/2025	10
54	100089	11/01/2025	10
55	100089	12/01/2025	10
56	100089	13/01/2025	10
57	100089	14/01/2025	10
58	100089	15/01/2025	10
59	100089	16/01/2025	10
60	100089	23/01/2025	10
61	100089	24/01/2025	10
62	100089	25/01/2025	10
63	100089	26/01/2025	10
64	100089	27/01/2025	10
65	100089	28/01/2025	10
66	100089	29/01/2025	10
67	100089	30/01/2025	10
68	100089	31/01/2025	10
69	100112	6/01/2025	10
70	100112	7/01/2025	10
71	100112	8/01/2025	10
72	100112	9/01/2025	10
73	100112	10/01/2025	10
74	100112	11/01/2025	10
75	100112	13/01/2025	10
76	100112	14/01/2025	10
77	100112	15/01/2025	10
78	100112	16/01/2025	10
79	100112	23/01/2025	10
80	100112	24/01/2025	10
81	100112	25/01/2025	10
82	100112	27/01/2025	10
83	100112	28/01/2025	10
84	100112	29/01/2025	10
85	100112	30/01/2025	10
86	100112	31/01/2025	10
87	100226	2/01/2025	8
88	100226	3/01/2025	8
89	100226	6/01/2025	8
90	100226	7/01/2025	8
91	100226	9/01/2025	8
92	100226	13/01/2025	8
93	100226	14/01/2025	8
94	100226	16/01/2025	8
95	100226	20/01/2025	8
96	100226	21/01/2025	8
97	100226	23/01/2025	8
98	100226	28/01/2025	8
99	100226	30/01/2025	8
100	100498	13/01/2025	8
101	100498	15/01/2025	8
102	100498	17/01/2025	8
103	100498	24/01/2025	8
104	100519	16/01/2025	10
105	100519	17/01/2025	10
106	100519	18/01/2025	10
107	100519	19/01/2025	10
108	100519	20/01/2025	10
109	100519	21/01/2025	10
110	100519	22/01/2025	10
111	100519	23/01/2025	10
112	100519	30/01/2025	10
113	100519	31/01/2025	10
114	101765	6/01/2025	10
115	101765	7/01/2025	10
116	101765	8/01/2025	10
117	101765	9/01/2025	10
118	101765	10/01/2025	10
119	101765	11/01/2025	10
120	101765	12/01/2025	10
121	101765	13/01/2025	10
122	101765	14/01/2025	10
123	101765	15/01/2025	10
124	101765	16/01/2025	10
125	101765	23/01/2025	10
126	101765	24/01/2025	10
127	101765	25/01/2025	10
128	101765	27/01/2025	10
129	101765	28/01/2025	10
130	101765	29/01/2025	10
131	101765	30/01/2025	10
132	101765	31/01/2025	10
133	102537	13/01/2025	10
134	102537	14/01/2025	10
135	102537	15/01/2025	10
136	102537	16/01/2025	10
137	102537	17/01/2025	10
138	102537	18/01/2025	10
139	102537	19/01/2025	10
140	102537	20/01/2025	10
141	102537	21/01/2025	10
142	102537	22/01/2025	10
143	102537	23/01/2025	10
144	102537	24/01/2025	10
145	102537	25/01/2025	10
146	102563	7/01/2025	10
147	102563	8/01/2025	10
148	102563	9/01/2025	10
149	102563	10/01/2025	10
150	102563	11/01/2025	10
151	102563	13/01/2025	10
152	102563	14/01/2025	10
153	102563	15/01/2025	10
154	102563	16/01/2025	10
155	102563	17/01/2025	10
156	102563	18/01/2025	10
157	102563	19/01/2025	10
158	102563	20/01/2025	10
159	102563	21/01/2025	10
160	102763	13/01/2025	10
161	102763	14/01/2025	10
162	102763	15/01/2025	10
163	102763	16/01/2025	10
164	102763	17/01/2025	10
165	102763	18/01/2025	10
166	102763	19/01/2025	10
167	102763	20/01/2025	10
168	102763	21/01/2025	10
169	102763	28/01/2025	10
170	102763	29/01/2025	10
171	102763	30/01/2025	10
172	102763	31/01/2025	10
173	103600	1/01/2025	8
174	103600	3/01/2025	8
175	103600	6/01/2025	8
176	103600	7/01/2025	8
177	103600	8/01/2025	8
178	103600	9/01/2025	8
179	103600	10/01/2025	8
180	103600	13/01/2025	8
181	103600	14/01/2025	8
182	103600	15/01/2025	8
183	103600	16/01/2025	8
184	103600	17/01/2025	8
185	103600	20/01/2025	8
186	103600	21/01/2025	8
187	103600	22/01/2025	8
188	103600	27/01/2025	8
189	103600	28/01/2025	8
190	103600	29/01/2025	8
191	103600	30/01/2025	8
192	103600	31/01/2025	8
193	103755	18/01/2025	10
194	103755	19/01/2025	10
195	103755	20/01/2025	10
196	103755	21/01/2025	10
197	103755	22/01/2025	10
198	103755	23/01/2025	10
199	103755	24/01/2025	10
200	103755	25/01/2025	10
201	103755	26/01/2025	10
202	103755	27/01/2025	10
203	103755	28/01/2025	10
204	103755	29/01/2025	10
205	103755	30/01/2025	10
206	103755	31/01/2025	10
207	104585	14/01/2025	10
208	104585	15/01/2025	10
209	104585	16/01/2025	10
210	104585	17/01/2025	10
211	104585	18/01/2025	10
212	104585	19/01/2025	10
213	104585	20/01/2025	10
214	104585	21/01/2025	10
215	104585	22/01/2025	10
216	104585	23/01/2025	10
217	104585	24/01/2025	10
218	104585	25/01/2025	10
219	104585	27/01/2025	10
220	104585	28/01/2025	10
221	107273	13/01/2025	8
222	107273	14/01/2025	8
223	107273	15/01/2025	8
224	107273	16/01/2025	8
225	107273	17/01/2025	8
226	107493	6/01/2025	10
227	107493	7/01/2025	10
228	107493	8/01/2025	10
229	107493	9/01/2025	10
230	107493	10/01/2025	10
231	107493	11/01/2025	10
232	107493	13/01/2025	10
233	107493	14/01/2025	10
234	107493	15/01/2025	10
235	107493	22/01/2025	10
236	107493	23/01/2025	10
237	107493	24/01/2025	10
238	107493	25/01/2025	10
239	107493	28/01/2025	10
240	107493	29/01/2025	10
241	107493	30/01/2025	10
242	107493	31/01/2025	10
243	107611	28/01/2025	10
244	107611	29/01/2025	10
245	107611	30/01/2025	10
246	107611	31/01/2025	10
247	107630	6/01/2025	10
248	107630	7/01/2025	10
249	107630	8/01/2025	10
250	107630	9/01/2025	10
251	107630	10/01/2025	10
252	107630	11/01/2025	10
253	107630	12/01/2025	10
254	107630	13/01/2025	10
255	107630	14/01/2025	10
256	107630	15/01/2025	10
257	107630	16/01/2025	10
258	107630	17/01/2025	10
259	107630	18/01/2025	10
260	107630	19/01/2025	10
261	107630	20/01/2025	10
262	107630	30/01/2025	10
263	107630	31/01/2025	10
264	107693	8/01/2025	10
265	107693	9/01/2025	10
266	107693	10/01/2025	10
267	107693	11/01/2025	10
268	107693	13/01/2025	10
269	107693	14/01/2025	10
270	107693	15/01/2025	10
271	107693	22/01/2025	10
272	107693	23/01/2025	10
273	107693	24/01/2025	10
274	107693	25/01/2025	10
275	107693	26/01/2025	10
276	107693	27/01/2025	10
277	107693	28/01/2025	10
278	107693	29/01/2025	10
279	107693	30/01/2025	10
280	107693	31/01/2025	10
281	107734	7/01/2025	10
282	107734	8/01/2025	10
283	107734	9/01/2025	10
284	107734	10/01/2025	10
285	107734	11/01/2025	10
286	107734	13/01/2025	10
287	107734	14/01/2025	10
288	107734	15/01/2025	10
289	107734	16/01/2025	10
290	107734	17/01/2025	10
291	107734	18/01/2025	10
292	107734	19/01/2025	10
293	107734	20/01/2025	10
294	107734	21/01/2025	10
295	107734	28/01/2025	10
296	107734	29/01/2025	10
297	107734	30/01/2025	10
298	107734	31/01/2025	10
299	107748	6/01/2025	10
300	107748	7/01/2025	10
301	107748	8/01/2025	10
302	107748	9/01/2025	10
303	107748	10/01/2025	10
304	107748	11/01/2025	10
305	107748	13/01/2025	10
306	107748	14/01/2025	10
307	107748	15/01/2025	10
308	107748	16/01/2025	10
309	107748	23/01/2025	10
310	107748	24/01/2025	10
311	107748	25/01/2025	10
312	107748	26/01/2025	10
313	107748	27/01/2025	10
314	107748	28/01/2025	10
315	107748	29/01/2025	10
316	107748	30/01/2025	10
Timesheet report

Timesheet vs travel data.xlsx
	A	B	C
1	Person ID	FLY IN DATE	FLY OUT DATE
2	108310	1/28/25	2/4/25
3	107833	1/14/25	1/21/25
4	102537	1/13/25	1/24/25
5	108220	1/9/25	1/23/25
6	108220	1/30/25	2/13/25
7	108098	1/9/25	1/23/25
8	108098	1/9/25	1/23/25
9	108098	1/17/25	1/31/25
10	108098	1/30/25	2/11/25
11	100519	1/16/25	1/23/25
12	100519	1/30/25	2/13/25
13	107969	1/15/25	1/22/25
14	107999	1/6/25	1/14/25
15	107999	1/21/25	2/4/25
16	108008	1/13/25	1/23/25
17	108008	1/30/25	2/13/25
18	107693	1/8/25	1/15/25
19	107693	1/22/25	2/5/25
20	108314	1/8/25	1/22/25
21	108314	1/29/25	2/12/25
22	107810	1/15/25	1/22/25
23	108121	1/28/25	2/11/25
24	108252	TBC	TBC
25	108252	TBC	TBC
26	107895	1/15/25	1/29/25
27	108006	One way	1/27/25
28	108006	One way	2/27/25
29	108006	One way	3/20/25
30	104585	1/14/25	1/28/25
31	102563	1/7/25	1/21/25
32	107809	1/16/25	2/20/25
33	107611	1/28/25	2/11/25
34	107611	ACCOMM ONLY START 18MAR25	3/25/25
35	100080	1/6/25	1/23/25
36	100080	1/30/25	2/12/25
37	108075	1/15/25	1/29/25
38	100089	1/6/25	1/16/25
39	100089	1/23/25	2/6/25
40	102763	1/13/25	1/21/25
41	102763	1/28/25	2/11/25
42	107893	1/15/25	1/29/25
43	107493	1/6/25	1/15/25
44	107493	1/22/25	2/5/25
45	100081	1/6/25	1/11/25
46	100081	1/27/25	2/11/25
47	107803	1/9/25	1/23/25
48	107803	1/30/25	2/13/25
49	107981	1/6/25	1/14/25
50	107981	1/21/25	2/4/25
51	107759	1/14/25	1/28/25
52	108200	1/6/25	1/14/25
53	108200	1/21/25	2/4/25
54	100112	1/6/25	1/16/25
55	100112	1/23/25	2/6/25
56	108149	1/16/25	1/30/25
57	107887	1/6/25	1/13/25
58	107887	1/20/25	1/30/25
59	107748	1/6/25	1/16/25
60	107748	1/23/25	2/6/25
61	107834	1/6/25	1/16/25
62	107834	1/23/25	2/6/25
63	108005	1/7/25	1/21/25
64	108005	1/7/25	1/21/25
65	108005	1/28/25	2/11/25
66	101765	1/6/25	1/16/25
67	101765	1/27/25	2/6/25
68	107630	1/6/25	1/20/25
69	107630	1/30/25	2/13/25
70	108075	1/9/25	1/22/25
71	108075	1/30/25	2/6/25
72	107885	1/6/25	1/11/25
73	107885	1/16/25	1/30/25
74	107273	1/13/25	1/16/25
75	108038	1/6/25	1/15/25
76	108038	1/22/25	2/5/25
77	107913	1/6/25	1/16/25
78	107913	1/23/25	2/6/25
79	103755	1/18/25	2/1/25
80	103755	TBC	TBC
81	108315	1/6/25	1/20/25
82	108315	1/27/25	2/10/25
83	107734	1/7/25	1/21/25
84	107734	1/28/25	2/11/25
Travel data

 

[kvsrinivasamurthy]

Try. In D2

=LET(a,'Travel data'!A2:A84,b,'Travel data'!B2:B84,c,'Travel data'!C2:C84,MAP('Timesheet report'!A2:A316,'Timesheet report'!B2:B316,LAMBDA(x,y,IF(SUMPRODUCT((a=x)*(b<=y)*(c>=y))>0,"YES","NO"))))

 

[FormR]

Hi, here's another option you could try:

=IF(COUNTIFS('Travel Data'!A:A,A2,'Travel Data'!B:B,"<="&B2,'Travel Data'!C:C,">="&B2),"Y","N")

It looks like your data might be in a formal Excel table, in which case you can't use a spill formula, but if that's not the case and you wanted one that spilled you could also try.

=IF(COUNTIFS('Travel Data'!A:A,A2:.A1000,'Travel Data'!B:B,"<="&B2:.B1000,'Travel Data'!C:C,">="&B2:.B1000),"Y","N")