# Date ranges in excel

#### tonyjyoo

##### Board Regular
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
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
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
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>

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