# 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  Register To Reply

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,"")  Register To Reply

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

Thanks. That seems to work!  Register To Reply

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  Register To Reply

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

that formula works very well .. thanks  Register To Reply