Counting shifts lost between two dates

[donh]

Hi Group,

I'm working on a sickness monitoring sheet and I have four columns
similar to below which provide a lookup to dates on duty. I've put
together a formula (might be long winded) to calculate total days off
between two dates but now need to refer to my lookup table and
calculate how many days lost between those two dates. Its got me
stumped so far, can anyone point me in the right direction please or
pass comment on my totals formula.

If I can get this to work I intended to have a couple of IF functions
to check if its a whole month to count through or part as already in
my totals.

As always any help would be great.


Regards

Don

Total formula
=IF($B4="","",
IF(AND(ISBLANK($C4),$B4<G$3,$B4>=F$3,$B$1>G$3),G$3-$B4+N("Counts from
sick day to today if fit day is null"),
IF(AND(ISBLANK($C4),$B4<G$3,$B$1>G$3),G$3-F$3+N("Counts from sick day
to today if fit day is null"),
IF(AND($B4<=F$3,$C4>F$3,$C4<=G$3),$C4-F$3+N("sick < = start Fit >
start but < end of month"),
IF(AND($B4>=F$3,$C4>F$3,$C4<G$3),$C4-$B4+N("sick >= start and fit <
month end"),
IF(AND($B4>=F$3,$B4<G$3,$C4>G$3),G$3-$B4+N("sick >= start but before
end fit after end"),
IF(AND($B4<F$3,$C4>G$3),G$3-F$3+N("sick before start fit after end"),

0)))))))



Shift lookup table

01/01/2007 1
02/01/2007 1
03/01/2007 1
04/01/2007 1
05/01/2007
06/01/2007
07/01/2007
08/01/2007
09/01/2007 1
10/01/2007 1
11/01/2007 1
12/01/2007 1
13/01/2007
14/01/2007
15/01/2007
16/01/2007
17/01/2007 1
18/01/2007 1
19/01/2007 1
20/01/2007 1
21/01/2007
22/01/2007

 

[Roger Govier]

Hi Don
Post an example of your source data
Try showing exactly what you have in each of your 4 columns

 

[donh]

Hi Roger,

I think the four columns might be clouding issue they are similar
repeats of shift pattern as already shown, copied below anyway.

If I can get this to work I plan on using an IF function to select my
sum range IF A1=Red C2:C500 for example.

I think my problem is being able to set the range of my SUMIF from the
date first sick to date fit. I'm trying to get multiple citeria to
work in sumif without success and am looking at newsgroup archives of
sumproduct, as yet without success.

If you need me to add more please say


Red Green Blue White
01/01/2007 D1 1 R3 R1 N1 1
02/01/2007 D2 1 R4 R2 N2 1
03/01/2007 N1 1 D1 1 R3 R1
04/01/2007 N2 1 D2 1 R4 R2
05/01/2007 R1 N1 1 D1 1 R3
06/01/2007 R2 N2 1 D2 1 R4
07/01/2007 R3 R1 N1 1 D1 1
08/01/2007 R4 R2 N2 1 D2 1
09/01/2007 D1 1 R3 R1 N1 1
10/01/2007 D2 1 R4 R2 N2 1
11/01/2007 N1 1 D1 1 R3 R1
12/01/2007 N2 1 D2 1 R4 R2
13/01/2007 R1 N1 1 D1 1 R3
14/01/2007 R2 N2 1 D2 1 R4
15/01/2007 R3 R1 N1 1 D1 1
16/01/2007 R4 R2 N2 1 D2 1
17/01/2007 D1 1 R3 R1 N1 1
18/01/2007 D2 1 R4 R2 N2 1
19/01/2007 N1 1 D1 1 R3 R1
20/01/2007 N2 1 D2 1 R4 R2
21/01/2007 R1 N1 1 D1 1 R3
22/01/2007 R2 N2 1 D2 1 R4
23/01/2007 R3 R1 N1 1 D1 1
24/01/2007 R4 R2 N2 1 D2 1
25/01/2007 D1 1 R3 R1 N1 1
26/01/2007 D2 1 R4 R2 N2 1
27/01/2007 N1 1 D1 1 R3 R1
28/01/2007 N2 1 D2 1 R4 R2
29/01/2007 R1 N1 1 D1 1 R3
30/01/2007 R2 N2 1 D2 1 R4
31/01/2007 R3 R1 N1 1 D1 1
01/02/2007 R4 R2 N2 1 D2 1
02/02/2007 D1 1 R3 R1 N1 1
03/02/2007 D2 1 R4 R2 N2 1
04/02/2007 N1 1 D1 1 R3 R1
05/02/2007 N2 1 D2 1 R4 R2
06/02/2007 R1 N1 1 D1 1 R3
07/02/2007 R2 N2 1 D2 1 R4
08/02/2007 R3 R1 N1 1 D1 1
09/02/2007 R4 R2 N2 1 D2 1
10/02/2007 D1 1 R3 R1 N1 1
11/02/2007 D2 1 R4 R2 N2 1
12/02/2007 N1 1 D1 1 R3 R1
13/02/2007 N2 1 D2 1 R4 R2
14/02/2007 R1 N1 1 D1 1 R3
15/02/2007 R2 N2 1 D2 1 R4
16/02/2007 R3 R1 N1 1 D1 1
17/02/2007 R4 R2 N2 1 D2 1
18/02/2007 D1 1 R3 R1 N1 1
19/02/2007 D2 1 R4 R2 N2 1
20/02/2007 N1 1 D1 1 R3 R1
21/02/2007 N2 1 D2 1 R4 R2
22/02/2007 R1 N1 1 D1 1 R3
23/02/2007 R2 N2 1 D2 1 R4
24/02/2007 R3 R1 N1 1 D1 1
25/02/2007 R4 R2 N2 1 D2 1
26/02/2007 D1 1 R3 R1 N1 1
27/02/2007 D2 1 R4 R2 N2 1
28/02/2007 N1 1 D1 1 R3 R1
01/03/2007 N2 1 D2 1 R4 R2
02/03/2007 R1 N1 1 D1 1 R3
03/03/2007 R2 N2 1 D2 1 R4
04/03/2007 R3 R1 N1 1 D1 1
05/03/2007 R4 R2 N2 1 D2 1
06/03/2007 D1 1 R3 R1 N1 1
07/03/2007 D2 1 R4 R2 N2 1
08/03/2007 N1 1 D1 1 R3 R1
09/03/2007 N2 1 D2 1 R4 R2
10/03/2007 R1 N1 1 D1 1 R3
11/03/2007 R2 N2 1 D2 1 R4
12/03/2007 R3 R1 N1 1 D1 1
13/03/2007 R4 R2 N2 1 D2 1
14/03/2007 D1 1 R3 R1 N1 1
15/03/2007 D2 1 R4 R2 N2 1
16/03/2007 N1 1 D1 1 R3 R1
17/03/2007 N2 1 D2 1 R4 R2
18/03/2007 R1 N1 1 D1 1 R3
19/03/2007 R2 N2 1 D2 1 R4
20/03/2007 R3 R1 N1 1 D1 1
21/03/2007 R4 R2 N2 1 D2 1
22/03/2007 D1 1 R3 R1 N1 1
23/03/2007 D2 1 R4 R2 N2 1
24/03/2007 N1 1 D1 1 R3 R1
25/03/2007 N2 1 D2 1 R4 R2
26/03/2007 R1 N1 1 D1 1 R3
27/03/2007 R2 N2 1 D2 1 R4
28/03/2007 R3 R1 N1 1 D1 1
29/03/2007 R4 R2 N2 1 D2 1
30/03/2007 D1 1 R3 R1 N1 1
31/03/2007 D2 1 R4 R2 N2 1
01/04/2007 N1 1 D1 1 R3 R1

 

[donh]

PS, I've got this to work for a single date criteria

IF(P1="Red",SUMIF(B4:B34,">="&M1,D434),"etc")

and just need to add <= but don't know how


Regards

Don

 

[Roger Govier]

Hi Don

I'm still lost.
What I see are 7 columns, not 4
There is a Date column - is this column A?
Then taking the first row of data only I see
A2 01/01/2007
B2 D1
C2 1
D2 R3
E2 R1
F2 N1
G2 1

Which column is supposed to be represented by Red, and Green and Blue and
White?
I am certain we can resolve your problem with Sumproduct (or even with a
Pivot Table) but as Yet I am not understanding what it is your are trying to
achieve.

If you would prefer, you could send me a copy of your file with explanation,
direct.
To send direct
roger at technology4u dot co dot uk
Do the obvious with at and dot.