check the last year for absence.

[BARACUTA]

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

 

[shippey121]

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

 

[Aladin Akyurek]

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]

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]

hi aladin
yes, i am using excel 2003
stuart

 

[Aladin Akyurek]

hi aladin
yes, i am using excel 2003
stuart

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

 

[BARACUTA]

hi again aladin,
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

 

[Aladin Akyurek]

hi again aladin,
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

EDATE requires the Analysis Toolpak add-in (see Tools|Add-Ins)...

 

[BARACUTA]

hi aladin
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

 

[Aladin Akyurek]

hi aladin
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"))