# Counting the number of periods (not days) of absence in Excel

1. ## Counting the number of periods (not days) of absence in Excel

Hi Everyone,

I'm putting together an absence spreadsheet, but I want a formula that will calculate the number of periods of absence (rather than number of individual days). e.g. Person A was off from 1 Oct-3 Oct, then on 14 Oct to 20 Oct. This person would have 10 days of absence for the month, but this would be 2 periods.

Any ideas?

PS. I'm using Excel 2007

2. ## Re: Counting the number of periods (not days) of absence in Excel

Try this formula in E3 copied down

=COUNTIFS(F3:AJ3,"s",G3:AK3,"")

3. ## Re: Counting the number of periods (not days) of absence in Excel

Thanks. That seems to work!

4. ## Re: Counting the number of periods (not days) of absence in Excel

hi
I've been looking for a way to do this for ages and this is simple and works perfectly except for one thing. if I put an "s" in AJ3 (the last day in the month) it reduces the periods by one (does not count the period) even if it is part of a longer period.
I've tried extending the formula by a couple of columns but it seems to ignore this column
thanks
Vicky

5. ## Re: Counting the number of periods (not days) of absence in Excel

that formula works very well .. thanks

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1