Date ranges in excel

tonyjyoo

Board Regular
Joined
Aug 5, 2016
Messages
167
Hello,

I have a data set bunch of work dates for a list of employees in 2018, with a breakdown of what's billable and what's non-billable days.




I also have travel reports to indicate date ranges that they were in London.



Is it possible to figure out of those days they were in London, how many of those days they worked (from data set 1) were billable vs non-billable? I imagine I would need to somehow look up the employee ID's.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
You could add a column to data set 1 to see if the date was "in London" then do a total of Billable and Non-Billable.

ABCDEFGHIJKL
NameStatusNameFromToNameBillableNon-Billable
John DoeBillableJohn DoeJohn Doe
Sally SmithNon-BillableSally SmithSally Smith
Bill DaylyBillableBill DaylyBill Dayly
John DoeNon-BillableJohn Doe
Sally SmithBillableSally Smith
Bill DaylyNon-BillableJohn Doe
John DoeBillable
Sally SmithNon-Billable
Bill DaylyBillable
John DoeNon-Billable
Sally SmithBillable
Bill DaylyNon-Billable

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Date[/TD]

[TD="align: center"]London[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]02-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]02-Feb-18[/TD]
[TD="align: center"]02-Feb-18[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]03-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]03-Feb-18[/TD]
[TD="align: center"]03-Feb-18[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]04-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]04-Feb-18[/TD]
[TD="align: center"]12-Feb-18[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]05-Feb-18[/TD]

[TD="align: center"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11-Feb-18[/TD]
[TD="align: center"]14-Feb-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]06-Feb-18[/TD]

[TD="align: center"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]07-Feb-18[/TD]
[TD="align: center"]16-Feb-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]07-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]08-Feb-18[/TD]
[TD="align: center"]08-Feb-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]08-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]09-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]10-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]11-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]12-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]13-Feb-18[/TD]

[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
TonyIyoo

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]=COUNTIFS(A:A,J2,D:D,1,C:C,"Billable")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]=COUNTIFS(A:A,J2,D:D,1,C:C,"Non-Billable")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2
[/TH]
[TD="align: left"]=COUNTIFS(F:F,A2,G:G,"<="&B2,H:H,">="&B2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You could add a column to data set 1 to see if the date was "in London" then do a total of Billable and Non-Billable.

ABCDEFGHIJKL
NameStatusNameFromToNameBillableNon-Billable
John DoeBillableJohn DoeJohn Doe
Sally SmithNon-BillableSally SmithSally Smith
Bill DaylyBillableBill DaylyBill Dayly
John DoeNon-BillableJohn Doe
Sally SmithBillableSally Smith
Bill DaylyNon-BillableJohn Doe
John DoeBillable
Sally SmithNon-Billable
Bill DaylyBillable
John DoeNon-Billable
Sally SmithBillable
Bill DaylyNon-Billable

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Date[/TD]

[TD="align: center"]London[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]02-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]02-Feb-18[/TD]
[TD="align: center"]02-Feb-18[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]03-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]03-Feb-18[/TD]
[TD="align: center"]03-Feb-18[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]04-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]04-Feb-18[/TD]
[TD="align: center"]12-Feb-18[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]05-Feb-18[/TD]

[TD="align: center"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11-Feb-18[/TD]
[TD="align: center"]14-Feb-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]06-Feb-18[/TD]

[TD="align: center"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]07-Feb-18[/TD]
[TD="align: center"]16-Feb-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]07-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]08-Feb-18[/TD]
[TD="align: center"]08-Feb-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]08-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]09-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]10-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]11-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]12-Feb-18[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]13-Feb-18[/TD]

[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
TonyIyoo

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]K2[/TH]
[TD="align: left"]=COUNTIFS(A:A,J2,D:D,1,C:C,"Billable")[/TD]
[/TR]
[TR]
[TH]L2[/TH]
[TD="align: left"]=COUNTIFS(A:A,J2,D:D,1,C:C,"Non-Billable")[/TD]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=COUNTIFS(F:F,A2,G:G,"<="&B2,H:H,">="&B2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks. This is close.

is it possible to take it 1 step further and calculate billable and nonbillable HOURS recorded on a given work date, then see in those travel date ranges, give me the respective billable/nonbill hours?
 
Upvote 0
You didn't supply any sample data so I'm guessing your data set 1 is in decimal hours for which I added a column.

ABCDEFGHIJKLM
NameStatusNameFromToNameBillableNon-Billable
John DoeBillableJohn DoeJohn Doe
Sally SmithNon-BillableSally SmithSally Smith
Bill DaylyBillableBill DaylyBill Dayly
John DoeNon-BillableJohn Doe
Sally SmithBillableSally Smith
Bill DaylyNon-BillableJohn Doe
John DoeBillable
Sally SmithNon-Billable
Bill DaylyBillable
John DoeNon-Billable
Sally SmithBillable
Bill DaylyNon-Billable
John DoeBillable

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Date[/TD]
[TD="align: center"]Hours[/TD]

[TD="align: center"]London[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]02-Feb-18[/TD]
[TD="align: center"]1.25[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]02-Feb-18[/TD]
[TD="align: center"]02-Feb-18[/TD]
[TD="align: right"][/TD]

[TD="align: right"]17.25[/TD]
[TD="align: right"]1.5[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]03-Feb-18[/TD]
[TD="align: center"]1.50[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]03-Feb-18[/TD]
[TD="align: center"]03-Feb-18[/TD]
[TD="align: right"][/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]5.25[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]04-Feb-18[/TD]
[TD="align: center"]8.00[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]04-Feb-18[/TD]
[TD="align: center"]12-Feb-18[/TD]
[TD="align: right"][/TD]

[TD="align: right"]9.25[/TD]
[TD="align: right"]1.5[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]05-Feb-18[/TD]
[TD="align: center"]3.75[/TD]

[TD="align: center"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11-Feb-18[/TD]
[TD="align: center"]14-Feb-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]06-Feb-18[/TD]
[TD="align: center"]1.25[/TD]

[TD="align: center"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]07-Feb-18[/TD]
[TD="align: center"]16-Feb-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]07-Feb-18[/TD]
[TD="align: center"]1.50[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]08-Feb-18[/TD]
[TD="align: center"]08-Feb-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]08-Feb-18[/TD]
[TD="align: center"]8.00[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]09-Feb-18[/TD]
[TD="align: center"]3.75[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]10-Feb-18[/TD]
[TD="align: center"]1.25[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]11-Feb-18[/TD]
[TD="align: center"]1.50[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]12-Feb-18[/TD]
[TD="align: center"]8.00[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]13-Feb-18[/TD]
[TD="align: center"]3.75[/TD]

[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]08-Feb-18[/TD]
[TD="align: center"]8.00[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
TonyIyoo

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]=SUMIFS(C:C,A:A,K2,E:E,1,D:D,"Billable")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]=SUMIFS(C:C,A:A,K2,E:E,1,D:D,"Non-Billable")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2
[/TH]
[TD="align: left"]=COUNTIFS(G:G,A2,H:H,"<="&B2,I:I,">="&B2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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