Work out amount of hours in given time slot

Alpacino

Well-known Member
Joined
Mar 16, 2011
Messages
511
Hi all,

i would like to workout how many hours per time slot.

but the format is in 1000-1900

the time slots will be:
0900-1200
1200-1800
1800:2200

so the answer will be :
02:00
06:00
01:00


thanks in advance
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi andrew thanks for the reply. How do I measure how many hours there are per time slot.

is there a formula I could use.

if someone works 1000-1900
between time slot 0900-1200 will be 02:00
between time slot 1200-1800 will be 06:00
between time slot 1800-2200 will be 01:00


thanks in advance
 
Upvote 0
Example (formula in B2 copied across):


Excel 2010
ABCD
10900-12001200-18001800-2200
21000-19002:006:001:00
Sheet5
Cell Formulas
RangeFormula
B2=TEXT(MIN(RIGHT($A2,4),RIGHT(B1,4)),"00\:00")-TEXT(MAX(LEFT($A2,4),LEFT(B1,4)),"00\:00")
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

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