Counting by combined start and end time
I have a list of employees whose start and end time are contained in the same cell, in the same column. The format looks like this: 11:00-21:30, but with varying start and end times. I need to keep that format for internal purposes. I need a formula that will count the number of employees starting before a specified time, say 11:00 for example. -- monger ------------------------------------------------------------------------ monger's Profile: http://www.excelforum.com/member.php...o&userid=17911 View this thread: http://www.excelforum.com/showthread...hreadid=511258 |
Counting by combined start and end time
I would create a new column and use a formula like:
=--LEFT(A2,SEARCH("-",A2)-1)) to extract the time. (format as time) Then I'd use =countif() against that column. =countif(b2:b8,"<="&time(11,0,0)) But you could do it in one formula: =SUMPRODUCT(--(TIMEVALUE(LEFT(A2:A8,SEARCH("-",A2:A8)-1))<=TIME(11,0,0))) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. monger wrote: I have a list of employees whose start and end time are contained in the same cell, in the same column. The format looks like this: 11:00-21:30, but with varying start and end times. I need to keep that format for internal purposes. I need a formula that will count the number of employees starting before a specified time, say 11:00 for example. -- monger ------------------------------------------------------------------------ monger's Profile: http://www.excelforum.com/member.php...o&userid=17911 View this thread: http://www.excelforum.com/showthread...hreadid=511258 -- Dave Peterson |
Counting by combined start and end time
Perfect! Thank you -- monger ------------------------------------------------------------------------ monger's Profile: http://www.excelforum.com/member.php...o&userid=17911 View this thread: http://www.excelforum.com/showthread...hreadid=511258 |
All times are GMT +1. The time now is 03:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com