Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Question Counting cells that have a time

Hello,

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.

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

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

Thank you!!
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,872
Default Counting cells that have a time

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
  #3   Report Post  
Junior Member
 
Posts: 2
Default

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!


Quote:
Originally Posted by Claus Busch View Post
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
counting cells within certain time frames - 24 hour clock lummox Excel Discussion (Misc queries) 4 May 5th 09 09:22 PM
Counting Time Spearhead Excel Discussion (Misc queries) 1 March 7th 09 08:54 PM
Counting time Lisa Excel Worksheet Functions 3 August 1st 08 02:05 AM
Counting Time littlejess22 Excel Worksheet Functions 3 June 12th 07 04:00 PM
Counting Cells with time value Anthony Excel Discussion (Misc queries) 2 February 9th 05 09:55 PM


All times are GMT +1. The time now is 09:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"