Counting cells that have a time

Discussion in 'Excel Beginners' started by Petteri, Feb 26, 2014.

  1. Petteri

    Petteri Guest

    Hello,

    I'm creating a spreadsheet to come up with an employee schedule. I'v
    gotten the basics down. I'm able to calculate the hours worked per wee
    and per day. What I'd like to do, to really make it easier to work wit
    going forward is to be able to count the number of staff scheduled fo
    any given shift, based on start time.

    I've only got basic excel knowledge and this seems to be beyond m
    ability to figure out. I've tried searching here and other places, bu
    just can't seem to find a solution. Hopefully someone here can assis
    me.

    Are attaching excel files here frowned upon? It doesn't seem to be a
    option. I can zip it if that helps.

    Thank you!


    --
    Petteri
     
    Petteri, Feb 26, 2014
    #1
    1. Advertisements

  2. Petteri

    Claus Busch Guest

    Hi,

    Am Wed, 26 Feb 2014 16:45:47 +0000 schrieb Petteri:

    > I'm creating a spreadsheet to come up with an employee schedule. I've
    > gotten the basics down. I'm able to calculate the hours worked per week
    > and per day. What I'd like to do, to really make it easier to work with
    > going forward is to be able to count the number of staff scheduled for
    > any given shift, based on start time.


    you can attach a zipped excel workbook

    If your times are in column B and you want to count the time 8:00 try:
    =COUNTIF(B1:B100,TIME(8,,))
    If your names are in column A and time in B you can also try for
    existing names at 8:00
    =SUMPRODUCT(--(B1:B100=TIME(8,,)),--(A1:A100<>""))


    Regards
    Claus B.
    --
    Win XP PRof SP2 / Vista Ultimate SP2
    Office 2003 SP2 /2007 Ultimate SP2
     
    Claus Busch, Feb 27, 2014
    #2
    1. Advertisements

  3. Petteri

    Petteri Guest

    Thanks,

    I solved it with a combination of COUNTIFs like this:

    =COUNTIF(D4:D7,">12:00")+COUNTIF(D17:D25,">12:00")-COUNTIF(D17:D25,">19:00")

    Thanks again!


    Claus Busch;1616462 Wrote:
    > Hi,
    >
    > Am Wed, 26 Feb 2014 16:45:47 +0000 schrieb Petteri:
    > -
    > > I'm creating a spreadsheet to come up with an employee schedule. I've
    > > gotten the basics down. I'm able to calculate the hours worked pe

    > week
    > > and per day. What I'd like to do, to really make it easier to wor

    > with
    > > going forward is to be able to count the number of staff schedule

    > for
    > > any given shift, based on start time. -

    >
    > you can attach a zipped excel workbook
    >
    > If your times are in column B and you want to count the time 8:00 try:
    > =COUNTIF(B1:B100,TIME(8,,))
    > If your names are in column A and time in B you can also try for
    > existing names at 8:00
    > =SUMPRODUCT(--(B1:B100=TIME(8,,)),--(A1:A100<>""))
    >
    >
    > Regards
    > Claus B.
    > --
    > Win XP PRof SP2 / Vista Ultimate SP2
    > Office 2003 SP2 /2007 Ultimate SP



    --
    Petteri
     
    Petteri, Feb 27, 2014
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Tim Foster

    counting cells

    Tim Foster, Jun 22, 2004, in forum: Excel Beginners
    Replies:
    3
    Views:
    89
  2. Kevin J Prince

    counting valid cells

    Kevin J Prince, Aug 26, 2004, in forum: Excel Beginners
    Replies:
    3
    Views:
    82
  3. Rosehill - ExcelForums.com

    Counting empty cells within a range of cells

    Rosehill - ExcelForums.com, Apr 7, 2005, in forum: Excel Beginners
    Replies:
    2
    Views:
    132
    Rosehill - ExcelForums.com
    May 2, 2005
  4. Rosehill - ExcelForums.com

    Counting empty cells within a range of cells

    Rosehill - ExcelForums.com, Apr 7, 2005, in forum: Excel Beginners
    Replies:
    0
    Views:
    130
    Rosehill - ExcelForums.com
    Apr 7, 2005
  5. Andrzej
    Replies:
    0
    Views:
    99
    Andrzej
    May 27, 2005
  6. David494

    Counting cells

    David494, Jul 28, 2005, in forum: Excel Beginners
    Replies:
    1
    Views:
    105
    Bob Phillips
    Jul 28, 2005
  7. Gnugs
    Replies:
    1
    Views:
    135
    Stefi
    Mar 7, 2007
  8. Eisaz
    Replies:
    15
    Views:
    170
    T. Valko
    Nov 19, 2008
Loading...