Time Formula

[JumboCactuar]

Hi,
if i have the following in the format below:

Shift Times:
08:00-17:00
08:00-17:00
08:00-17:00
08:00-17:00
08:00-17:00
08:30-17:30
08:30-17:30
08:30-17:30
08:30-17:30
08:30-17:30
09:00-18:00
09:30-18:30
10:00-19:00

Hour - answers in red which i need formula for (Total hours from available users - example hour 08 - 5 users start at 08:00 = 5 hours plus 5 start at 08:30 = 2.5)
08 - 7.5
09 - 12
10 - 13

Ones who start 08:00 and 08:30 are added to 09:00 as its within their hours

Thanks for any help, i know this looks confusing. I think im best to start converting these into numeric values like 08:00 = 80000 and 08:30 = 80500

 

[inactiveuserps07]

Re: Need help with Time Formula please

Thanks WBD. For some reason, part of the formula keeps getting truncated. I'll try again:

<c2,if((timevalue(mid(a$2:a$14,find("-",a$2:a$14)+1,10))*24)>=SUM(IF(VALUE(LEFT(A$2:A$14,2)) < C2,IF((TIMEVALUE(MID(A$2:A$14,7,10))*24) > C2,1,0),0))+SUM(IF(VALUE(LEFT(A$2:A$14,2))=C2,IF(VALUE(MID(A$2:A$14,4,2))=0,IF((TIMEVALUE(MID(A$2:A$14,7,10))*24) > C2,1,0),0),0))+SUM(IF(VALUE(LEFT(A$2:A$14,2))=C2,IF(VALUE(MID(A$2:A$14,4,2))=30,IF((TIMEVALUE(MID(A$2:A$14,7,10))*24) > C2,0.5,0),0),0))

Yup. Even in the CODE tags the "<" were being treated as HTML markers. Had to add spaces to get it to read properly.
@JumboCactuar, sorry for the confusion there. This site has some glitches.</c2,if((timevalue(mid(a$2:a$14,find("-",a$2:a$14)+1,10))*24)>

 

[inactiveuserps07]

Re: Need help with Time Formula please

@JumboCactuar, I combined @wideboydixon's formula with a second half in line with mine to produce a formula that I believe will now be accurate both for time clocked in and time clocked out (again, be sure to confirm with Ctrl+Shift+Enter):

=COUNTIFS($A$2:$A$14,"<="&TEXT(TIME($C2,0,0),"hh:mm")&"-9")+(COUNTIF($A$2:$A$14,TEXT(TIME($C2,30,0),"hh:mm")&"*")/2)-SUM(IF(VALUE(MID($A$2:$A$14,7,2)) <= C2,1,0))+SUM(IF(VALUE(MID($A$2:$A$14,7,2))=C2,IF(MID($A$2:$A$14,10,2)="30",0.5,0)))

 

[wideboydixon]

Re: Need help with Time Formula please

Haha. I did something similar:

Book1
	A	B	C	D
1	Shift Times:		Hour	Available
2	08:00-17:00		08	7.5
3	08:00-17:00		09	11.5
4	08:00-17:00		10	13
5	08:00-17:00		11	13
6	08:00-17:00		12	13
7	08:30-17:30		13	13
8	08:30-17:30		14	13
9	08:30-17:30		15	13
10	08:30-17:30		16	13
11	08:30-17:30		17	5.5
12	09:00-18:00		18	1.5
13	09:30-18:30		19	0
14	10:00-19:00		20	0
Sheet1

Cell Formulas
Range	Formula
D2	{=SUM(IF(TIMEVALUE(LEFT($A$2:$A$14,5))<=TIME($C2,0,0),1,0))+SUM(IF(TIMEVALUE(LEFT($A$2:$A$14,5))=TIME($C2,30,0),1,0))/2-SUM(IF(TIME($C2,0,0)>=TIMEVALUE(RIGHT($A$2:$A$14,5)),1,0))-SUM(IF(TIMEVALUE(RIGHT($A$2:$A$14,5))=TIME($C2,30,0),1,0))/2}
Press CTRL+SHIFT+ENTER to enter array formulas.

It's a beast of a formula

WBD

 

[JumboCactuar]

Re: Need help with Time Formula please

Thanks for the effort guys, impressive formula

Working perfectly

 

[Nishant94]

Re: Need help with Time Formula please

Using the same data setup as @wideboydixon, you can use this:

=SUMPRODUCT(COUNTIF($A$2:$A$14,TEXT(C$1:C2,"00")&"*"))-
         COUNTIF($A$2:$A$14,TEXT(C2,"00")&":30*")/2

 

[wideboydixon]

Re: Need help with Time Formula please

Using the same data setup as @wideboydixon, you can use this:

=SUMPRODUCT(COUNTIF($A$2:$A$14,TEXT(C$1:C2,"00")&"*"))-
COUNTIF($A$2:$A$14,TEXT(C2,"00")&":30*")/2

As stated, that doesn't take into account the finish times of the shifts. In the hour 18:00-19:00 for example, there are only 1.5 man hours available.

WBD

 

[inactiveuserps07]

Re: Need help with Time Formula please

@JumboCactuar, glad we got it sorted. Group effort.