Excel formula question

[blazzzercat via OfficeKB.com]

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.

 

[Bernard Liengme]

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

 

[blazzzercat via OfficeKB.com]

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

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.

 

[Bernard Liengme]

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(A66))*(ISTEXT((B6:Q6))))
Carefully not A66 followed by B66 in the second term
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

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