# how much annual leave taken

#### Rogerisit

##### Board Regular
Hello!
I am trying to calculate how much annual leave "Bob" had during the week ending 6/1/2017. This sheet hasn't pasted so well, sorry. The "a" on the attendance sheet sits in A7. But I need to know for each entire week, and this formula needs to work for all employees. I'm stuck!! Thank you for any help

 January 2017 Present A Annual Leave S Sick Leave U Unpaid Leave W Weekend worked Sun 01 Mon 02 Tue 03 Wed 04 Thu 05 Fri 06 Sat 07 Sun 08 Mon 09 Tue 10 Wed 11 Thu 12 Fri 13 Sat 14 Sun 15 Mon 16 Tue 17 Wed 18 Thu 19 Fri 20 Sat 21 Sun 22 Mon 23 Tue 24 Wed 25 Thu 26 Fri 27 Sat 28 Sun 29 Mon 30 Tue 31 Bob s s A p p Jim Frank Arthur Mary Doris Betty Nancy Charles Annie

<colgroup><col span="2"><col span="31"></colgroup><tbody>
</tbody>

<colgroup><col span="2"><col span="31"></colgroup><tbody>
</tbody>

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Eric W

##### MrExcel MVP
I took the liberty of rearranging your sheet a little. If this isn't what you had in mind, let me know, and I'll try to adapt.

ABCDEFGHIJKLMNOPQRS
1January 2017
2These totals are for the week ending:1/13/2017
3PASUW
4PresentAnnual LeaveSick LeaveUnpaid LeaveWeekend worked
51/1/20171/2/20171/3/20171/4/20171/5/20171/6/20171/7/20171/8/20171/9/20171/10/20171/11/20171/12/20171/13/2017
6Bob50000ssappppppp
7Jim30002pppppwwppp
8Frank32000ppppaaappp
9Arthur00500aaaaasssss
10Mary30200ssssppspsp
11Doris50000wpppppppppp
12Betty00050appppuuuuu
13Nancy30200ppspppppss
14Charles40000ppppapppp
15Annie41000ppppuapppp

</tbody>
Sheet10

Worksheet Formulas
CellFormula
B6=COUNTIFS(\$G\$5:\$AK\$5,"<="&\$E\$2,\$G\$5:\$AK\$5,">="&\$E\$2-6,\$G6:\$AK6,B\$3)

</tbody>

<tbody>
</tbody>

The five values you had listed are in B3:F3, with the description below. The week you are interested in will be in E2. The list of dates is for January, which goes to column AK. I assume you'd want to carry that out to Dec 31, but I don't show all that. The dates in E2 and in row 5 are all Excel dates. Then you can enter the formula in B6, and copy it to the right to F6, and down as far as necessary. The totals will all adjust when you change the value in E2.

Let me know if this helps.

#### Rogerisit

##### Board Regular
Thank you.
I have copy and pasted your formula (and updated cell references) I keep getting an error #name?. Any thoughts?

#### Rogerisit

##### Board Regular
No point. I have learned that excel 2003 doesn't support countifs!! I'm looking forward to an upgrade one day. Apparently sumproduct can help fill this void. I'd gladly accept a sumproduct formula! Thanks for your help Eric

#### Eric W

##### MrExcel MVP
The SUMPRODUCT equivalent is:

B6: =SUMPRODUCT(--(\$G\$5:\$AK\$5<=\$E\$2),--(\$G\$5:\$AK\$5>=\$E\$2-6),--(\$G6:\$AK6=B\$3))

This works in my copy of Excel 2000, so it should work in 2003. Of course, Excel 2003 only allows 256 columns per sheet, so you can't put the entire year on one sheet. One month per sheet, plus probably a few extra days of the next month so you get a full week in.

Good luck!

#### Rogerisit

##### Board Regular
Thanks Eric. Yes, I discovered that as I first created 3 months and then tried to get the formula to work. I have turned it vertical, but have been following your formula and then just changing the cell references, ie date range. Stupid limitations!! I have entered this formula you have given me and it is only giving me the number of "a" (B3) in Jan 6 box, which of course is 1. My formula looks like this: =SUMPRODUCT(--(Attendance!A6:A381<=Bob!A5),--(Attendance!A6:A381>=Bob!A5),--(Attendance!B6:B381=Attendance!\$D\$2))

#### Rogerisit

##### Board Regular
I missed the "-6". Thanks so much. REALLY appreciate your help

#### Rogerisit

##### Board Regular
Now I have another problem: half days. So Bob has half annual leave, half work day. a/p or p/a. Clearly I have no idea what I'm doing as =SUMPRODUCT(--(Attendance!A6:A381<=Bob!A5),--(Attendance!A6:A381>=Bob!A5-6),--(Attendance!B6:B381=Attendance!\$D\$2),--(Attendance!B6:B381="a/"?""),----(Attendance!B6:B381=""?"/a")) Doesn't work! Any thoughts on that one? These thing just snowball.

#### Rogerisit

##### Board Regular
I've managed to spread it out and can get it to work, except the ""?"/a" thing doesn't work

Replies
8
Views
278
Replies
0
Views
829
Replies
3
Views
280
Replies
11
Views
601
Replies
6
Views
648

1,191,693
Messages
5,988,131
Members
440,126
Latest member
Dee8

### 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.

### Which adblocker are you using?

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

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