JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
720
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

inactiveuserps07

Banned user
Joined
May 25, 2017
Messages
748
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:

Code:
=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

Well-known Member
Joined
Nov 16, 2016
Messages
720
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
=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:
e12e5540b5.png


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)>
 
Last edited:

inactiveuserps07

Banned user
Joined
May 25, 2017
Messages
748
Re: Need help with Time Formula please

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

Code:
=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)>
 
Last edited:

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
720
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Re: Need help with Time Formula please

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

Code:
=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

Banned user
Joined
May 25, 2017
Messages
748
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:

Code:
[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

Well-known Member
Joined
Jun 2, 2016
Messages
3,401

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

Banned user
Joined
May 25, 2017
Messages
748
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.
 
Last edited:

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
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

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Re: Need help with Time Formula please

Hi @ErikTyler,

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

WBD
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top