+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    11-01-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    3

    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
    Attached Files Attached Files
    Last edited by petedacreep; 11-01-2012 at 08:30 AM.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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,"")
    Audere est facere

  3. #3
    Registered User
    Join Date
    11-01-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    3

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

    Thanks. That seems to work!

  4. #4
    Registered User
    Join Date
    01-22-2015
    Location
    england
    MS-Off Ver
    7
    Posts
    1

    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. #5
    Registered User
    Join Date
    04-30-2010
    Location
    oxford england
    MS-Off Ver
    Excel 2007
    Posts
    10

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

    that formula works very well .. thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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