Timesheet vs travel dates

A

alessiaexcels

New Member
Joined
Jul 29, 2024
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1Person IDDateHours BookedOnsite? (Y or N)
21000726/01/20258
31000727/01/20258
41000729/01/20258
510007214/01/20258
610007215/01/20258
710007216/01/20258
810007220/01/20258
910007221/01/20258
1010007222/01/20258
1110007223/01/20258
1210007229/01/20258
1310007230/01/20258
141000806/01/202510
151000807/01/202510
161000808/01/202510
171000809/01/202510
1810008010/01/202510
1910008011/01/202510
2010008013/01/202510
2110008014/01/202510
2210008015/01/202510
2310008016/01/202510
2410008017/01/202510
2510008018/01/202510
2610008019/01/202510
2710008020/01/202510
2810008021/01/202510
2910008022/01/202510
3010008023/01/202510
3110008030/01/202510
3210008031/01/202510
331000816/01/202510
341000817/01/202510
351000818/01/202510
361000819/01/202510
3710008110/01/202510
3810008111/01/202510
3910008113/01/20258
4010008114/01/20258
4110008115/01/20258
4210008116/01/20258
4310008117/01/20258
4410008127/01/202510
4510008128/01/202510
4610008129/01/202510
4710008130/01/202510
4810008131/01/202510
491000896/01/202510
501000897/01/202510
511000898/01/202510
521000899/01/202510
5310008910/01/202510
5410008911/01/202510
5510008912/01/202510
5610008913/01/202510
5710008914/01/202510
5810008915/01/202510
5910008916/01/202510
6010008923/01/202510
6110008924/01/202510
6210008925/01/202510
6310008926/01/202510
6410008927/01/202510
6510008928/01/202510
6610008929/01/202510
6710008930/01/202510
6810008931/01/202510
691001126/01/202510
701001127/01/202510
711001128/01/202510
721001129/01/202510
7310011210/01/202510
7410011211/01/202510
7510011213/01/202510
7610011214/01/202510
7710011215/01/202510
7810011216/01/202510
7910011223/01/202510
8010011224/01/202510
8110011225/01/202510
8210011227/01/202510
8310011228/01/202510
8410011229/01/202510
8510011230/01/202510
8610011231/01/202510
871002262/01/20258
881002263/01/20258
891002266/01/20258
901002267/01/20258
911002269/01/20258
9210022613/01/20258
9310022614/01/20258
9410022616/01/20258
9510022620/01/20258
9610022621/01/20258
9710022623/01/20258
9810022628/01/20258
9910022630/01/20258
10010049813/01/20258
10110049815/01/20258
10210049817/01/20258
10310049824/01/20258
10410051916/01/202510
10510051917/01/202510
10610051918/01/202510
10710051919/01/202510
10810051920/01/202510
10910051921/01/202510
11010051922/01/202510
11110051923/01/202510
11210051930/01/202510
11310051931/01/202510
1141017656/01/202510
1151017657/01/202510
1161017658/01/202510
1171017659/01/202510
11810176510/01/202510
11910176511/01/202510
12010176512/01/202510
12110176513/01/202510
12210176514/01/202510
12310176515/01/202510
12410176516/01/202510
12510176523/01/202510
12610176524/01/202510
12710176525/01/202510
12810176527/01/202510
12910176528/01/202510
13010176529/01/202510
13110176530/01/202510
13210176531/01/202510
13310253713/01/202510
13410253714/01/202510
13510253715/01/202510
13610253716/01/202510
13710253717/01/202510
13810253718/01/202510
13910253719/01/202510
14010253720/01/202510
14110253721/01/202510
14210253722/01/202510
14310253723/01/202510
14410253724/01/202510
14510253725/01/202510
1461025637/01/202510
1471025638/01/202510
1481025639/01/202510
14910256310/01/202510
15010256311/01/202510
15110256313/01/202510
15210256314/01/202510
15310256315/01/202510
15410256316/01/202510
15510256317/01/202510
15610256318/01/202510
15710256319/01/202510
15810256320/01/202510
15910256321/01/202510
16010276313/01/202510
16110276314/01/202510
16210276315/01/202510
16310276316/01/202510
16410276317/01/202510
16510276318/01/202510
16610276319/01/202510
16710276320/01/202510
16810276321/01/202510
16910276328/01/202510
17010276329/01/202510
17110276330/01/202510
17210276331/01/202510
1731036001/01/20258
1741036003/01/20258
1751036006/01/20258
1761036007/01/20258
1771036008/01/20258
1781036009/01/20258
17910360010/01/20258
18010360013/01/20258
18110360014/01/20258
18210360015/01/20258
18310360016/01/20258
18410360017/01/20258
18510360020/01/20258
18610360021/01/20258
18710360022/01/20258
18810360027/01/20258
18910360028/01/20258
19010360029/01/20258
19110360030/01/20258
19210360031/01/20258
19310375518/01/202510
19410375519/01/202510
19510375520/01/202510
19610375521/01/202510
19710375522/01/202510
19810375523/01/202510
19910375524/01/202510
20010375525/01/202510
20110375526/01/202510
20210375527/01/202510
20310375528/01/202510
20410375529/01/202510
20510375530/01/202510
20610375531/01/202510
20710458514/01/202510
20810458515/01/202510
20910458516/01/202510
21010458517/01/202510
21110458518/01/202510
21210458519/01/202510
21310458520/01/202510
21410458521/01/202510
21510458522/01/202510
21610458523/01/202510
21710458524/01/202510
21810458525/01/202510
21910458527/01/202510
22010458528/01/202510
22110727313/01/20258
22210727314/01/20258
22310727315/01/20258
22410727316/01/20258
22510727317/01/20258
2261074936/01/202510
2271074937/01/202510
2281074938/01/202510
2291074939/01/202510
23010749310/01/202510
23110749311/01/202510
23210749313/01/202510
23310749314/01/202510
23410749315/01/202510
23510749322/01/202510
23610749323/01/202510
23710749324/01/202510
23810749325/01/202510
23910749328/01/202510
24010749329/01/202510
24110749330/01/202510
24210749331/01/202510
24310761128/01/202510
24410761129/01/202510
24510761130/01/202510
24610761131/01/202510
2471076306/01/202510
2481076307/01/202510
2491076308/01/202510
2501076309/01/202510
25110763010/01/202510
25210763011/01/202510
25310763012/01/202510
25410763013/01/202510
25510763014/01/202510
25610763015/01/202510
25710763016/01/202510
25810763017/01/202510
25910763018/01/202510
26010763019/01/202510
26110763020/01/202510
26210763030/01/202510
26310763031/01/202510
2641076938/01/202510
2651076939/01/202510
26610769310/01/202510
26710769311/01/202510
26810769313/01/202510
26910769314/01/202510
27010769315/01/202510
27110769322/01/202510
27210769323/01/202510
27310769324/01/202510
27410769325/01/202510
27510769326/01/202510
27610769327/01/202510
27710769328/01/202510
27810769329/01/202510
27910769330/01/202510
28010769331/01/202510
2811077347/01/202510
2821077348/01/202510
2831077349/01/202510
28410773410/01/202510
28510773411/01/202510
28610773413/01/202510
28710773414/01/202510
28810773415/01/202510
28910773416/01/202510
29010773417/01/202510
29110773418/01/202510
29210773419/01/202510
29310773420/01/202510
29410773421/01/202510
29510773428/01/202510
29610773429/01/202510
29710773430/01/202510
29810773431/01/202510
2991077486/01/202510
3001077487/01/202510
3011077488/01/202510
3021077489/01/202510
30310774810/01/202510
30410774811/01/202510
30510774813/01/202510
30610774814/01/202510
30710774815/01/202510
30810774816/01/202510
30910774823/01/202510
31010774824/01/202510
31110774825/01/202510
31210774826/01/202510
31310774827/01/202510
31410774828/01/202510
31510774829/01/202510
31610774830/01/202510
Timesheet report


Timesheet vs travel data.xlsx
ABC
1Person IDFLY IN DATEFLY OUT DATE
21083101/28/252/4/25
31078331/14/251/21/25
41025371/13/251/24/25
51082201/9/251/23/25
61082201/30/252/13/25
71080981/9/251/23/25
81080981/9/251/23/25
91080981/17/251/31/25
101080981/30/252/11/25
111005191/16/251/23/25
121005191/30/252/13/25
131079691/15/251/22/25
141079991/6/251/14/25
151079991/21/252/4/25
161080081/13/251/23/25
171080081/30/252/13/25
181076931/8/251/15/25
191076931/22/252/5/25
201083141/8/251/22/25
211083141/29/252/12/25
221078101/15/251/22/25
231081211/28/252/11/25
24108252TBCTBC
25108252TBCTBC
261078951/15/251/29/25
27108006One way1/27/25
28108006One way2/27/25
29108006One way3/20/25
301045851/14/251/28/25
311025631/7/251/21/25
321078091/16/252/20/25
331076111/28/252/11/25
34107611ACCOMM ONLY START 18MAR253/25/25
351000801/6/251/23/25
361000801/30/252/12/25
371080751/15/251/29/25
381000891/6/251/16/25
391000891/23/252/6/25
401027631/13/251/21/25
411027631/28/252/11/25
421078931/15/251/29/25
431074931/6/251/15/25
441074931/22/252/5/25
451000811/6/251/11/25
461000811/27/252/11/25
471078031/9/251/23/25
481078031/30/252/13/25
491079811/6/251/14/25
501079811/21/252/4/25
511077591/14/251/28/25
521082001/6/251/14/25
531082001/21/252/4/25
541001121/6/251/16/25
551001121/23/252/6/25
561081491/16/251/30/25
571078871/6/251/13/25
581078871/20/251/30/25
591077481/6/251/16/25
601077481/23/252/6/25
611078341/6/251/16/25
621078341/23/252/6/25
631080051/7/251/21/25
641080051/7/251/21/25
651080051/28/252/11/25
661017651/6/251/16/25
671017651/27/252/6/25
681076301/6/251/20/25
691076301/30/252/13/25
701080751/9/251/22/25
711080751/30/252/6/25
721078851/6/251/11/25
731078851/16/251/30/25
741072731/13/251/16/25
751080381/6/251/15/25
761080381/22/252/5/25
771079131/6/251/16/25
781079131/23/252/6/25
791037551/18/252/1/25
80103755TBCTBC
811083151/6/251/20/25
821083151/27/252/10/25
831077341/7/251/21/25
841077341/28/252/11/25
Travel data
 
Sort by date Sort by votes
Try. In D2
Excel Formula: 
=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"))))
 
Upvote 0
Hi, here's another option you could try:
Excel Formula: 
=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.
Excel Formula: 
=IF(COUNTIFS('Travel Data'!A:A,A2:.A1000,'Travel Data'!B:B,"<="&B2:.B1000,'Travel Data'!C:C,">="&B2:.B1000),"Y","N")
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
how to compute days between dates excel with per month
Replies
9
Views
167
FormR
FormR
S
  • Question Question
VBA Separate Day and Time to Another Column if condition meet
2
Replies
10
Views
165
SamKhem
S
S
  • Question Question
Calculate Weekly movements
2
Replies
13
Views
148
Shp1
S
K
  • Question Question
Excel VBA for Data Handling
Replies
1
Views
110
MikeVol
MikeVol
S
  • Solved
Value Extraction
Replies
8
Views
112
Peter_SSs
Peter_SSs

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