Annual leave - list days off
 

Help! (please)

I've looked at using INDEX & MATCH etc for these but can't quite get what I want.

I have a list of staff's leave each month, set out as below. Members of staff do not always work the same number of hours, so a day off is denoted as their number of hours, so below this is 7.5. I need to have a function/formula that returns which days a person has had off. For example in the sample below I would like a result of "Mon 01 Feb 16, Wed 03 Feb 16, Tue 09 Feb 16." It wouldn't matter if these results were in individual cells as I could concatenate.

As a refinement (and if I'm not asking too much) if someone has a half-day it's denoted as 3.75 - could we cater for that too?

Col C Col D
Row 1 Employee/Date Hours per days
row 2 John Mills
row 3
row 4 Mon 01 Feb 16 7.5
row 5 Tue 02 Feb 16
row 6 Wed 03 Feb 16 7.5
row 7 Thu 04 Feb 16
row 8 Fri 05 Feb 16
row 9 Sat 06 Feb 16
row 10 Sun 07 Feb 16
row 11 Mon 08 Feb 16
row 12 Tue 09 Feb 16 7.5
row 13 Wed 10 Feb 16
row 14 Thu 11 Feb 16
row 15 Fri 12 Feb 16



Thanks in advance!

Steve

Annual leave - list days off
 

Hi Steve,

Am Wed, 27 Jan 2016 23:52:10 -0800 (PST) schrieb
:

Col C Col D
Row 1 Employee/Date Hours per days
row 2 John Mills
row 3
row 4 Mon 01 Feb 16 7.5
row 5 Tue 02 Feb 16
row 6 Wed 03 Feb 16 7.5
row 7 Thu 04 Feb 16
row 8 Fri 05 Feb 16
row 9 Sat 06 Feb 16
row 10 Sun 07 Feb 16
row 11 Mon 08 Feb 16
row 12 Tue 09 Feb 16 7.5
row 13 Wed 10 Feb 16
row 14 Thu 11 Feb 16
row 15 Fri 12 Feb 16

try:
=IFERROR(INDEX(C:C,SMALL(IF($D$1:$D$100=7.5,ROW($1 :$100)),ROW(A1))),"")
and enter the array formula with CTRL+Shift+Enter and copy down until
the cell is empty.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Annual leave - list days off
 

Hi Claus

thanks for the fast response - it words perfectly. I've adapted a little, as in reality, the hours worked per day (7.5 figure) for each employee is held in Row 3.

={IFERROR(INDEX($C:$C,SMALL(IF(D$4:D$34=D$3,ROW($4 :$34)),ROW(A1))),"")}

If I wanted to take into account half-days (so in the example above D3/2) how would I do that? I've tried nesting an "OR" statement with the IF but don't get the correct result.

Cheers!

Steve

Annual leave - list days off
 

Hi Steve,

Am Thu, 28 Jan 2016 01:30:34 -0800 (PST) schrieb
:

If I wanted to take into account half-days (so in the example above D3/2) how would I do that? I've tried nesting an "OR" statement with the IF but don't get the correct result.

try:

=IFERROR(INDEX(C:C,SMALL(IF(($D$4:$D$34=7.5)+($D$4 :$D$34=3.75),ROW($4:$34)),ROW(A1))),"")
and enter with CTRL+Shift+Enter


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Annual leave - list days off
 

Magnificent! Thanks Claus, it's spot-on. :o)

Annual leave - list days off
 

Hi Claus

You helped me with several great pieces of information in the past. I hope you are managing to keep well in this strange coronavirus situation?

I hope you don't mind me contacting you - well hopefully I'll be contacting you, if you see this! Please say so if you do mind, or if this contravenes Group rules. I used to have your email address but it got lost in a pc upgrade!

I've posted a tricky question on the functions group and was wondering if you could have a look for me. If it's going to be too time-consuming let me know.

Most important in these weird times - keep safe and stay well.

Steve

Annual leave - list days off
 

Hi Claus

You helped me with several great pieces of information in the past. I hope you are managing to keep well in this strange coronavirus situation?

I hope you don't mind me contacting you - well hopefully I'll be contacting you, if you see this! Please say so if you do mind, or if this contravenes Group rules. I used to have your email address but it got lost in a pc upgrade :o(

I've posted a tricky question on the functions group and was wondering if you could have a look for me. If it's going to be too time-consuming let me know.

Here is a link to it:- https://groups.google.com/forum/#!to...ns/vm-JCwVgRGo

Most important in these weird times - keep safe and stay well.

Steve

Annual leave - list days off
 

Hi Claus

You helped me with several great pieces of information in the past. I hope you are managing to keep well in this strange coronavirus situation?

I hope you don't mind me contacting you - well hopefully I'll be contacting you, if you see this! Please say so if you do mind, or if this contravenes Group rules. I used to have your email address but it got lost in a pc upgrade!

I've posted a tricky question on the functions group and was wondering if you could have a look for me. If it's going to be too time-consuming let me know.

Here is a link to iy:- https://groups.google.com/forum/#!to...ns/vm-JCwVgRGo

Most important in these weird times - keep safe and stay well.

Steve