Remember Me?

#1
February 26th 14, 04:45 PM
 Junior Member First recorded activity by ExcelBanter: Feb 2014 Posts: 2
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
February 27th 14, 08:39 AM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,785
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
February 27th 14, 12:17 PM
 Junior Member First recorded activity by ExcelBanter: Feb 2014 Posts: 2

Thanks,

I solved it with a combination of COUNTIFs like this:

=COUNTIF(D47,"12:00")+COUNTIF(D1725,"12:00")-COUNTIF(D1725,"19:00")

Thanks again!

Quote:
 Originally Posted by Claus Busch 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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post lummox Excel Discussion (Misc queries) 4 May 5th 09 09:22 PM Spearhead Excel Discussion (Misc queries) 1 March 7th 09 07:54 PM Lisa Excel Worksheet Functions 3 August 1st 08 02:05 AM littlejess22 Excel Worksheet Functions 3 June 12th 07 04:00 PM Anthony Excel Discussion (Misc queries) 2 February 9th 05 08:55 PM

All times are GMT +1. The time now is 12:51 AM.