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.
 

Toadstool

Active Member
Joined
Mar 5, 2018
Messages
296
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>
 

tonyjyoo

Board Regular
Joined
Aug 5, 2016
Messages
167
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?
 

Toadstool

Active Member
Joined
Mar 5, 2018
Messages
296
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>
 

Forum statistics

Threads
1,077,772
Messages
5,336,150
Members
399,067
Latest member
CJWFM

Some videos you may like

This Week's Hot Topics

Top