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

@JumboCactuar, first, it seems to me that your 09 figure should be 11.5, not 12 (i.e., 11 people fully on by 9:00 and then add 0.5 for the person who came on at 9:30).

That said, assuming that your Shift Times header is in A1 with the shifts in A2:A14, and assuming your hourly list (08 - 19) begins in C2, the following array "mega-formula" (confirmed with Ctrl+Shift+Enter) in D2 and drag-copied down should work:

=SUM(IF(VALUE(LEFT(A$2:A$14,2))<C2,IF((TIMEVALUE(MID(A$2:A$14,FIND("-",A$2:A$14)+1,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,FIND("-",A$2:A$14)+1,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,FIND("-",A$2:A$14)+1,10))*24)>C2,0.5,0),0),0))

 

[JumboCactuar]

Re: Need help with Time Formula please

@JumboCactuar, first, it seems to me that your 09 figure should be 11.5, not 12 (i.e., 11 people fully on by 9:00 and then add 0.5 for the person who came on at 9:30).

That said, assuming that your Shift Times header is in A1 with the shifts in A2:A14, and assuming your hourly list (08 - 19) begins in C2, the following array "mega-formula" (confirmed with Ctrl+Shift+Enter) in D2 and drag-copied down should work:

=SUM(IF(VALUE(LEFT(A$2:A$14,2))<c2,if((timevalue(mid(a$2:a$14,find("-",a$2:a$14)+1,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,FIND("-",A$2:A$14)+1,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,FIND("-",A$2:A$14)+1,10))*24)>C2,0.5,0),0),0))

thanks for the reply, i tried this but it throws an error:

after error the first C2 in the formula is highlighted, maybe an error there? =SUM(IF(VALUE(LEFT(A$2:A$14,2))<c2,if((timevalue(mid(a$2:a$14,find("-",a$2:a$14)+1,10))*24) style="color: rgb(51, 51, 51); font-size: 12px;">C2,1,0)</c2,if((timevalue(mid(a$2:a$14,find("-",a$2:a$14)+1,10))*24)>

put the shift times in A2 downwards
and hours 06 onwards in C2</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, yes, somehow a less-than sign got cut from that formula when I pasted it:

=SUM(IF(VALUE(LEFT(A$2:A$14,2))[B][COLOR=#0000ff]<[/COLOR][/B]<c2,if((timevalue(mid(a$2:a$14,find("-",a$2:a$14)+1,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,FIND("-",A$2:A$14)+1,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,FIND("-",A$2:A$14)+1,10))*24)>C2,0.5,0),0),0))</c2,if((timevalue(mid(a$2:a$14,find("-",a$2:a$14)+1,10))*24)>

 

[JumboCactuar]

ADVERTISEMENT

Re: Need help with Time Formula please

@JumboCactuar, yes, somehow a less-than sign got cut from that formula when I pasted it:

=SUM(IF(VALUE(LEFT(A$2:A$14,2))[B][COLOR=#0000ff]<[/COLOR][/B]<c2,if((timevalue(mid(a$2:a$14,find("-",a$2:a$14)+1,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,FIND("-",A$2:A$14)+1,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,FIND("-",A$2:A$14)+1,10))*24)>C2,0.5,0),0),0))</c2,if((timevalue(mid(a$2:a$14,find("-",a$2:a$14)+1,10))*24)>

same error but now at this stage:
=SUM(IF(VALUE(LEFT(A$2:A$14,2))<C2,1,0),0))

 

[inactiveuserps07]

Re: Need help with Time Formula please

And if your shift times will always look exactly as they do, you can shorten it a bit to this:

[COLOR=#333333]=SUM(IF(VALUE(LEFT(A$2:A$14,2))[/COLOR][B]<[/B]<c2,if((timevalue(mid(a$2:a$14,find("-",a$2:a$14)+1,10))*24) style="color: rgb(51, 51, 51); font-size: 12px;">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))</c2,if((timevalue(mid(a$2:a$14,find("-",a$2:a$14)+1,10))*24)>

 

[wideboydixon]

ADVERTISEMENT

Re: Need help with Time Formula please

How about this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;color: #333333;;">Shift Times:</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Hour</td><td style="font-weight: bold;;">Available</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #333333;;">08:00-17:00</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">08</td><td style="text-align: right;;">7.5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="color: #333333;;">08:00-17:00</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">09</td><td style="text-align: right;;">11.5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="color: #333333;;">08:00-17:00</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">10</td><td style="text-align: right;;">13</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="color: #333333;;">08:00-17:00</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="color: #333333;;">08:00-17:00</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="color: #333333;;">08:30-17:30</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="color: #333333;;">08:30-17:30</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="color: #333333;;">08:30-17:30</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="color: #333333;;">08:30-17:30</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="color: #333333;;">08:30-17:30</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="color: #333333;;">09:00-18:00</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="color: #333333;;">09:30-18:30</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="color: #333333;;">10:00-19:00</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$114,"<="&TEXT(<font color="Red">TIME(<font color="Green">$C2,0,0</font>),"hh:mm"</font>)&"-9"</font>)+COUNTIF(<font color="Blue">$A$2:$A$114,TEXT(<font color="Red">TIME(<font color="Green">$C2,30,0</font>),"hh:mm"</font>)&"*"</font>)/2</td></tr></tbody></table></td></tr></table><br />

WBD

 

[inactiveuserps07]

Re: Need help with Time Formula please

@JumboCactuar, as far as I can see, there are no instances where three right parentheses follow that formula portion. Are you remembering to use Ctrl+Shift+Enter after every time you edit?

And my formula is longer than others because you have to account for who goes OFF shift as the hours increase throughout the day.

 

[wideboydixon]

Re: Need help with Time Formula please

Having said that, it won't produce a value of 1.5 for hour 18 which you might want ...

WBD

 

[wideboydixon]

Re: Need help with Time Formula please

Hi @ErikTyler,

The first SUM() in your formula has 4 opening parentheses and 5 closing ones.

WBD