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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
1NameDateStatusLondonNameFromToNameBillableNon-Billable
2John Doe02-Feb-18Billable1John Doe02-Feb-1802-Feb-18John Doe21
3Sally Smith03-Feb-18Non-Billable1Sally Smith03-Feb-1803-Feb-18Sally Smith12
4Bill Dayly04-Feb-18Billable1Bill Dayly04-Feb-1812-Feb-18Bill Dayly21
5John Doe05-Feb-18Non-Billable0John Doe11-Feb-1814-Feb-18
6Sally Smith06-Feb-18Billable0Sally Smith07-Feb-1816-Feb-18
7Bill Dayly07-Feb-18Non-Billable1John Doe08-Feb-1808-Feb-18
8John Doe08-Feb-18Billable1
9Sally Smith09-Feb-18Non-Billable1
10Bill Dayly10-Feb-18Billable1
11John Doe11-Feb-18Non-Billable1
12Sally Smith12-Feb-18Billable1
13Bill Dayly13-Feb-18Non-Billable0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
TonyIyoo

Worksheet Formulas
CellFormula
K2=COUNTIFS(A:A,J2,D:D,1,C:C,"Billable")
L2=COUNTIFS(A:A,J2,D:D,1,C:C,"Non-Billable")
D2
=COUNTIFS(F:F,A2,G:G,"<="&B2,H:H,">="&B2)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
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
1NameDateStatusLondonNameFromToNameBillableNon-Billable
2John Doe02-Feb-18Billable1John Doe02-Feb-1802-Feb-18John Doe21
3Sally Smith03-Feb-18Non-Billable1Sally Smith03-Feb-1803-Feb-18Sally Smith12
4Bill Dayly04-Feb-18Billable1Bill Dayly04-Feb-1812-Feb-18Bill Dayly21
5John Doe05-Feb-18Non-Billable0John Doe11-Feb-1814-Feb-18
6Sally Smith06-Feb-18Billable0Sally Smith07-Feb-1816-Feb-18
7Bill Dayly07-Feb-18Non-Billable1John Doe08-Feb-1808-Feb-18
8John Doe08-Feb-18Billable1
9Sally Smith09-Feb-18Non-Billable1
10Bill Dayly10-Feb-18Billable1
11John Doe11-Feb-18Non-Billable1
12Sally Smith12-Feb-18Billable1
13Bill Dayly13-Feb-18Non-Billable0

<tbody>
</tbody>
TonyIyoo

Worksheet Formulas
CellFormula
K2=COUNTIFS(A:A,J2,D:D,1,C:C,"Billable")
L2=COUNTIFS(A:A,J2,D:D,1,C:C,"Non-Billable")
D2=COUNTIFS(F:F,A2,G:G,"<="&B2,H:H,">="&B2)

<tbody>
</tbody>

<tbody>
</tbody>


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
1NameDateHoursStatusLondonNameFromToNameBillableNon-Billable
2John Doe02-Feb-181.25Billable1John Doe02-Feb-1802-Feb-18John Doe17.251.5
3Sally Smith03-Feb-181.50Non-Billable1Sally Smith03-Feb-1803-Feb-18Sally Smith85.25
4Bill Dayly04-Feb-188.00Billable1Bill Dayly04-Feb-1812-Feb-18Bill Dayly9.251.5
5John Doe05-Feb-183.75Non-Billable0John Doe11-Feb-1814-Feb-18
6Sally Smith06-Feb-181.25Billable0Sally Smith07-Feb-1816-Feb-18
7Bill Dayly07-Feb-181.50Non-Billable1John Doe08-Feb-1808-Feb-18
8John Doe08-Feb-188.00Billable1
9Sally Smith09-Feb-183.75Non-Billable1
10Bill Dayly10-Feb-181.25Billable1
11John Doe11-Feb-181.50Non-Billable1
12Sally Smith12-Feb-188.00Billable1
13Bill Dayly13-Feb-183.75Non-Billable0
14John Doe08-Feb-188.00Billable1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
TonyIyoo

Worksheet Formulas
CellFormula
L2=SUMIFS(C:C,A:A,K2,E:E,1,D:D,"Billable")
M2=SUMIFS(C:C,A:A,K2,E:E,1,D:D,"Non-Billable")
E2
=COUNTIFS(G:G,A2,H:H,"<="&B2,I:I,">="&B2)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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