Excel formula question
 

This is for an Excel attendance worksheet. Entries are made for each day an
employee calls in as being absent (calloff).

What I am looking for is if 2 €œcalloffs€� happen in a row they only count as 1
€˜Occurrence
(multiple day call ins only count as 1 occurrence.)

If employee calls off on Feb 2 and calls again on Feb 3, this in only one
€˜Occurrence (they are only allowed 3 occurrences). But if they call in on
Feb 5th this is a 2nd occurrence.

I am looking for a formula to automate the counting of occurences.

Thanks so much.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200702/1

Excel formula question
 

I think we need to know how the data is set out.
Is it like this:
Fred 2-feb
George 2-feb
Mary 3-feb
Fred 5-feb
Or
Fred 3-feb 5-feb
Or what?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"blazzzercat via OfficeKB.com" <u30352@uwe wrote in message
news:6d83b1d562d31@uwe...
This is for an Excel attendance worksheet. Entries are made for each day
an
employee calls in as being absent (calloff).

What I am looking for is if 2 "calloffs" happen in a row they only count
as 1
'Occurrence'
(multiple day call ins only count as 1 occurrence.)

If employee calls off on Feb 2 and calls again on Feb 3, this in only one
'Occurrence' (they are only allowed 3 occurrences). But if they call in
on
Feb 5th this is a 2nd occurrence.

I am looking for a formula to automate the counting of occurences.

Thanks so much.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200702/1

Excel formula question
 

Bernard,

Each employee has their own worksheet. A reason type is put into each date
cell. If two or more days in succession are entered occurence is counted as
one. If a date is skipped each one is counted as an occurence.

Monday Tuesday Wednesday......
x x = 1 occurence

x x = 2 occurences
Total occurences 3

Bernard Liengme wrote:
I think we need to know how the data is set out.
Is it like this:
Fred 2-feb
George 2-feb
Mary 3-feb
Fred 5-feb
Or
Fred 3-feb 5-feb
Or what?
best wishes
This is for an Excel attendance worksheet. Entries are made for each day
an
[quoted text clipped - 13 lines]

Thanks so much.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200702/1

Excel formula question
 

With dates in A1:Q1
And text codes in A2:A2
This formula seem to do the job
I counts how many blocks of text there are
=SUMPRODUCT(--(ISTEXT(A6:Q6)))-SUMPRODUCT((ISTEXT(A6:P6))*(ISTEXT((B6:Q6))))
Carefully not A6:P6 followed by B6:P6 in the second term
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
I think we need to know how the data is set out.
Is it like this:
Fred 2-feb
George 2-feb
Mary 3-feb
Fred 5-feb
Or
Fred 3-feb 5-feb
Or what?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"blazzzercat via OfficeKB.com" <u30352@uwe wrote in message
news:6d83b1d562d31@uwe...
This is for an Excel attendance worksheet. Entries are made for each day
an
employee calls in as being absent (calloff).

What I am looking for is if 2 "calloffs" happen in a row they only count
as 1
'Occurrence'
(multiple day call ins only count as 1 occurrence.)

If employee calls off on Feb 2 and calls again on Feb 3, this in only one
'Occurrence' (they are only allowed 3 occurrences). But if they call in
on
Feb 5th this is a 2nd occurrence.

I am looking for a formula to automate the counting of occurences.

Thanks so much.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200702/1