check the last year for absence.

BARACUTA

New Member
Joined
Oct 22, 2006
Messages
6
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
Joined
Mar 21, 2006
Messages
1,001
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Oct 22, 2006
Messages
6
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
Joined
Oct 22, 2006
Messages
6

ADVERTISEMENT

hi aladin
yes, i am using excel 2003
stuart
 

BARACUTA

New Member
Joined
Oct 22, 2006
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Oct 22, 2006
Messages
6
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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"))
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top