find minutes within time range for given range

mtharnden

Board Regular
Joined
Aug 17, 2011
Messages
114
hello, there is an equasion i made a long time ago that i cannot find or remember so i need help

i have employee schedules that i need to how many minutes they work in different windows
as you will see in the example schedules i have schedule formats that very widely so cant use a simple left/right and their start/end times vary widely.
if the start or end in the middle of a window i only want to know the amount of minutes that they are on the schedule during that window.
i know to find the techs start and end time since the format changes i can use left for the start time since it is always on the left and search to find "am" for number of characters and for the end time the same idea with a mid but my formula was getting way to long and complicated and i only had the first window done which was the easiest. there has to be an easier way

the windows are:
8am-12pm
12pm-5pm
5pm-7pm

example schedules are (and the formatting varries):
08 AM - 04 PM

<colgroup><col width="250"></colgroup><tbody>
</tbody>
07:15 AM - 05:00 PM

<tbody>
</tbody>
7:15 AM - 05:00 PM (7:15AM - 5:00PM)

<tbody>
</tbody>
7:00 AM - 07:05 AM (Training)

<tbody>
</tbody>
8 AM - 03 PM (Level 1)

<tbody>
</tbody>

<tbody>
</tbody>
09 AM - 03 PM

<tbody>
</tbody>


to make it easier for me, the schedule is in column Q and the minutes per timeframe will be in columns X/Y/Z (in order of time)
hopefully this is enough without attatching the actual file since i cant do that, thanks in advance for the help.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
=(C1*24*60)-(B1*24*60)

abcd
108 AM - 04 PM8:00 AM4:00 PM480
20
307:15 AM - 05:00 PM7:15 AM5:00 PM585
40
57:15 AM - 05:00 PM (7:15AM - 5:00PM)
7:15 AM5:00 PM585
60
77:00 AM - 07:05 AM (Training)7:00 AM7:05 AM5
80
98 AM - 03 PM (Level 1)8:00 AM3:00 PM420
100
110
1209 AM - 03 PM9:00 AM3:00 PM360

<tbody>
</tbody>
try this in d1 copied down
 
Last edited:
Upvote 0
=(C1*24*60)-(B1*24*60)

abcd
108 AM - 04 PM8:00 AM4:00 PM480
20
307:15 AM - 05:00 PM7:15 AM5:00 PM585
40
57:15 AM - 05:00 PM (7:15AM - 5:00PM)7:15 AM5:00 PM585
60
77:00 AM - 07:05 AM (Training)7:00 AM7:05 AM5
80
98 AM - 03 PM (Level 1)8:00 AM3:00 PM420
100
110
1209 AM - 03 PM9:00 AM3:00 PM360

<tbody>
</tbody>
try this in d1 copied down

i need to know the minutes within the time window not their wholse schedule
this is what i was using for finding the minutes however i still have to cut it down to just within the time window and i still have to format what you would have in B and C in this example --> =MINUTE(C1-B1)+(HOUR(C1-B1)*60)
 
Upvote 0
08 AM - 04 PM8:00 AM4:00 PM240 | 240
0
07:15 AM - 05:00 PM7:15 AM5:00 PM285 | 300
0
7:15 AM - 05:00 PM (7:15AM - 5:00PM)7:15 AM5:00 PM285 | 300
0
7:00 AM - 07:05 AM (Training)7:00 AM7:05 AM5
0
8 AM - 03 PM (Level 1)8:00 AM3:00 PM240 | 180
0
0
09 AM - 03 PM9:00 AM3:00 PM180 | 180

<tbody>
</tbody>
Code:
=IF(MATCH($B1,$H$2:$H$5,1)<>MATCH($C1,$H$2:$H$5,1),((VLOOKUP(C1,$H$3:$H$5,1,TRUE)-B1)*60*24)&" | "&((C1-VLOOKUP(C1,$H$3:$H$5,1,TRUE))*24*60),((C1-B1)*60*24))
try this one I hope this is what your looking for
I have it put a "|" between shifts
this is the table that I made in H1:H5
window time
12:00 AM
8:00 AM
12:00 PM
7:00 PM

<colgroup><col></colgroup><tbody>
</tbody>
 
Last edited:
Upvote 0
08 AM - 04 PM8:00 AM4:00 PM240 | 240
0
07:15 AM - 05:00 PM7:15 AM5:00 PM285 | 300
0
7:15 AM - 05:00 PM (7:15AM - 5:00PM)7:15 AM5:00 PM285 | 300
0
7:00 AM - 07:05 AM (Training)7:00 AM7:05 AM5
0
8 AM - 03 PM (Level 1)8:00 AM3:00 PM240 | 180
0
0
09 AM - 03 PM9:00 AM3:00 PM180 | 180

<tbody>
</tbody>
Code:
=IF(MATCH($B1,$H$2:$H$5,1)<>MATCH($C1,$H$2:$H$5,1),((VLOOKUP(C1,$H$3:$H$5,1,TRUE)-B1)*60*24)&" | "&((C1-VLOOKUP(C1,$H$3:$H$5,1,TRUE))*24*60),((C1-B1)*60*24))
try this one I hope this is what your looking for
I have it put a "|" between shifts
this is the table that I made in H1:H5
window time
12:00 AM
8:00 AM
12:00 PM
7:00 PM

<tbody>
</tbody>

this gets me closer however for the 8am-12pm it is counting the hour of 7am also, if i have a schedule 7am-7pm it put all the minutes in the first window
 
Upvote 0
ok i made an example to show what i need but cant figure out how to attatch


column B/C i prefer not to have but not a problem if i need to there is one error in it though.
columns D/E/F are the ones i need to populate

A
B
CDEF
1Scheduled TimeStart TimeEnd Time8-1212-55-7
208 AM - 04 PM08 AM04 PM2402400
307:15 AM - 05:00 PM07:15 AM05:00 PM2403000
47:15 AM - 05:00 PM (7:15AM - 5:00PM)7:15 AM05:00 PM2403000
57:00 AM - 07:05 AM (Training)7:00 AM#VALUE!000
68 AM - 03 PM (Level 1)8 AM03 PM2402400
709 AM - 03 PM09 AM03 PM1802400
810 AM - 07 PM10 AM07 PM120300120
910:00 AM - 07:00 PM10:00 AM07:00 PM120300120
1010:00 AM - 07:00 PM (10:00AM - 7:00PM)10:00 AM07:00 PM120300120
11
12
13
14example schedule timeequation for start/end (prefer NOT to have these columns) error for schedule not going into PMEnd Goal to get minutes within window
15
16
17

<colgroup><col style="mso-width-source:userset;mso-width-alt:1353;width:28pt" width="37"> <col style="mso-width-source:userset;mso-width-alt:11300;width:232pt" width="309"> <col style="mso-width-source:userset;mso-width-alt:3986; width:82pt" span="2" width="109"> <col style="width:48pt" span="3" width="64"> </colgroup><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
abcdefghij
1Scheduled TimeStart timeEnd TimeWindow 1window 2Window 3window numberwindow time
27 am- 7 pm7:00 AM12:01 PM30010window 012:00 AM
38 am - 4pm8:00 AM4:00 PM2402400window 18:00 AM
47:15am-5 pm7:15 AM5:00 PM2853000window 212:00 PM
57:15am-5 pm7:15 AM5:00 PM2853000window 35:00 PM
67 am- 5pm7:00 AM5:00 PM3003000window 47:00 PM
78 am - 3 pm8:00 AM3:00 PM2401800
89 am- 3 p9:00 AM3:00 PM1801800
910 am -7pm10:00 AM7:00 PM120300120
1010 am -7pm10:00 AM7:00 PM120300120
1110 am -7pm10:00 AM7:00 PM120300120
129 am - 3pm9:00 AM3:00 PM1801800

<tbody>
</tbody>
this is in D2 copied down
Code:
=(IF(C2>0.5,INDEX($I$2:$I$6,MATCH(IF(C2>0.5,0.5,C2),$I$2:$I$6,1)),C2)-B2)*24*60

this is in e2 copied down

Code:
=(IF(MATCH(C2,$I$2:$I$6)<3,0,IF(MATCH(C2,$I$2:$I$6)>3,INDEX($I$2:$I$6,4),C2))-IF(MATCH(B2,$I$2:$I$6)<3,INDEX($I$2:$I$6,3),0))*24*60

this is in f2 copied down

Code:
=(IF(MATCH(C2,$I$2:$I$6)<4,0,C2)-IF(MATCH(C2,$I$2:$I$6)<4,0,IF(MATCH(B2,$I$2:$I$6)<4,INDEX($I$2:$I$6,4),B2)))*24*60

please tell me if this works
 
Upvote 0
abcdefghij
1Scheduled TimeStart timeEnd TimeWindow 1window 2Window 3window numberwindow time
27 am- 7 pm7:00 AM12:01 PM30010window 012:00 AM
38 am - 4pm8:00 AM4:00 PM2402400window 18:00 AM
47:15am-5 pm7:15 AM5:00 PM2853000window 212:00 PM
57:15am-5 pm7:15 AM5:00 PM2853000window 35:00 PM
67 am- 5pm7:00 AM5:00 PM3003000window 47:00 PM
78 am - 3 pm8:00 AM3:00 PM2401800
89 am- 3 p9:00 AM3:00 PM1801800
910 am -7pm10:00 AM7:00 PM120300120
1010 am -7pm10:00 AM7:00 PM120300120
1110 am -7pm10:00 AM7:00 PM120300120
129 am - 3pm9:00 AM3:00 PM1801800

<tbody>
</tbody>
this is in D2 copied down
Code:
=(IF(C2>0.5,INDEX($I$2:$I$6,MATCH(IF(C2>0.5,0.5,C2),$I$2:$I$6,1)),C2)-B2)*24*60

this is in e2 copied down

Code:
=(IF(MATCH(C2,$I$2:$I$6)<3,0,IF(MATCH(C2,$I$2:$I$6)>3,INDEX($I$2:$I$6,4),C2))-IF(MATCH(B2,$I$2:$I$6)<3,INDEX($I$2:$I$6,3),0))*24*60

this is in f2 copied down

Code:
=(IF(MATCH(C2,$I$2:$I$6)<4,0,C2)-IF(MATCH(C2,$I$2:$I$6)<4,0,IF(MATCH(B2,$I$2:$I$6)<4,INDEX($I$2:$I$6,4),B2)))*24*60

please tell me if this works

still not working. D populates however it is still showing all minutes before noon, like row 2 in your example should be 240 since the window is 8am-12 and he works 7-7 he only has 240 minutes that he works within that window.
E and F i am getting NA errors
 
Upvote 0
i had some time to play with this and got working formulas please see if you can see a way to clean the formula or if you see any potential errors please let me know
this is keeping columns A-C as i had them before

column D
iferror is for any error, it checks that the end time is sometime after noon and if so adds the minutes, if greater than 240 caps at that
Code:
=IFERROR(IF(IF(RIGHT(C2,2)="PM",("12:00 pm"-B2),"")*1440>240,240,IF(RIGHT(C2,2)="PM",("12:00 pm"-B2),"")*1440),"")

column E
checks that start time is before noon and if so adds minutes, if greater than 300 caps at that
Code:
=IFERROR(IF(IF(RIGHT(B2,2)="AM",(C2-"12:00 pm")*1440,"")>300,300,IF(RIGHT(B2,2)="AM",(C2-"12:00 pm")*1440,"")),"")


column F
adds end time after 5pm, if negative shows zero if poitive adds up, if greater than 120 caps at that
Code:
=IFERROR(IF((C2-"5:00 pm")*1440<0,0,IF((C2-"5:00 pm")*1440>120,120,(C2-"5:00 pm")*1440)),"")
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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
Back
Top