# check the last year for absence.

#### BARACUTA

##### New Member
hi,
i would like some help if possible. i want a worksheet that can count how many days someone has been off work sick for the past 12 months. this count has to update every day, so that it always displays the number of days sickness for the past 12 months from the current date. is this possible, and if so, how. the worksheet currently uses 2 cells for each date, one that displays the date and the one to the right that is for entering an "s" for sick or "l" for lieu day, or "h" for holiday etc. how can i check the cells to the right of each date for an "s" and count them for the past 12 months?
thanks stuart

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### shippey121

##### Well-known Member
Stuart

welcome to the board,

just to clarify, the 12 months you are instersted in, is 12 months from todays date or 12 month in the year, what i mean is my companys work year runs from April to March, we calculate sickness from the todays date but only back to 1st April

could you also provide more detail on your layout, are you using columns and entering data downwards

##### MrExcel MVP
hi,
i would like some help if possible. i want a worksheet that can count how many days someone has been off work sick for the past 12 months. this count has to update every day, so that it always displays the number of days sickness for the past 12 months from the current date. is this possible, and if so, how. the worksheet currently uses 2 cells for each date, one that displays the date and the one to the right that is for entering an "s" for sick or "l" for lieu day, or "h" for holiday etc. how can i check the cells to the right of each date for an "s" and count them for the past 12 months?
thanks stuart

Supposing that column A houses dates from A2 on an column B status qualifiers...

1] In C2 enter:

=MATCH(9.99999999999999E+307,A:A)

2] In C3 enter:

=TODAY()

3] In C4 enter:

=SUMPRODUCT(--(\$A\$2:INDEX(A:A,C2)>=EDATE(C3,-12)),--(\$A\$2:INDEX(A:A,C2)<=C3),--(\$B\$2:INDEX(B:B,C2)="s"))

Are you on Excel 2003?

#### BARACUTA

##### New Member
hi
i am interested in 12 months preceding the current date. that is, i want to check today from 22/10/06 - 22/10/05, then automatically check tomorrow from 23/10/06 - 23/10/05 etc. etc.
i have set the worksheet out as 56 columns wide, covering 28 days using 2 cells for each day (the first cell diplays the date the cell to the right of it is for entering an "s" for sickness). the sheet is 130 rows deep, covering 10 years from 2005 - 2015.
thanks stuart

#### BARACUTA

##### New Member

yes, i am using excel 2003
stuart

##### MrExcel MVP
yes, i am using excel 2003
stuart

You need also specify the current range of 56 columns...

#### BARACUTA

##### New Member

i created a new worksheetto test your solution. i entered the date in column a (starting with 1/1/05 in a2 and going down to 1/1/15) i left column b empty for status qualifiers. i entered your formulae in c2, c3 & c4. in c2 i got 3661, in c3 i got todays date, & in c4 i got #name error. did i do something wrong?
thanks stuart

##### MrExcel MVP
i created a new worksheetto test your solution. i entered the date in column a (starting with 1/1/05 in a2 and going down to 1/1/15) i left column b empty for status qualifiers. i entered your formulae in c2, c3 & c4. in c2 i got 3661, in c3 i got todays date, & in c4 i got #name error. did i do something wrong?
thanks stuart

#### BARACUTA

##### New Member
i loaded the add in and your formula works. it's exactly what i wanted thank you. just one last question, why do you use 9.99999999e + 307? i can't see the significance of those numbers.
stuart

##### MrExcel MVP
i loaded the add in and your formula works. it's exactly what i wanted thank you. just one last question, why do you use 9.99999999e + 307? i can't see the significance of those numbers.
stuart

The MATCH idiom with that big number determines the last numeric record in column A. The SumProduct formula uses that information so that it always will calculate on all records whenever the data area changes.

Since you are on Excel 2003, you can change the data area in A:B into a list by means of Data|List|Create List.

If you take the foregoing step, the MATCH idiom in C2 is no longer needed.

Assuming that A1:B400 is the current range with A1:B1 housing labels, the formula in C4 would be just:

=SUMPRODUCT(--(\$A\$2:\$A\$400>=EDATE(C3,-12)),--(\$A\$2:\$A\$400<=C3),--(\$B\$2:\$B\$400="s"))

Replies
1
Views
109
Replies
19
Views
706
Replies
7
Views
124
Replies
31
Views
456
Replies
3
Views
98

1,136,421
Messages
5,675,759
Members
419,585
Latest member
popsin

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