Calculating time blocks

uk-rob

New Member
Joined
Apr 11, 2009
Messages
6
This one has puzzled me for hours and hours...it's finally time to get some help!

I am trying to calculate the amount of time in blocks, using a pre-determined spread. For example:

AM block is anytime between 0600-1230
PM block is anytime between 1230-1830
Evening block is anytime between 1830-0000 and the Overnight block is anytime between 0000-0600

Sounds easy enough, yeah? Let me paint an example of how I want it to calculate though:

If an employee starts at 0600 and finishes at 1400, then the AM total is 6.5 and the PM total is 1.5.

If an employee starts at 1100 and finishes at 1900, then the AM total is 1.5, the PM is 6 and the Evening is 0.5.

If an employee starts at 2200 and finishes at 0800, then all of the 10 hours must report to the Overnight total, even though some of them fall within the Evening and AM blocks.

I just can't seem to get each block to show the accurate number of hours...and to bring in the rules that I require.

Any help would be much appreciated...in the meantime I'll keep going!

Many thanks,

Rob.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Diablo II

Well-known Member
Joined
Sep 28, 2008
Messages
538
ok for one 12:00 noon is pm, but first set up your cells to work with time like [HH]MM in the time area for the total just use [HH] and if you clock in at 22:00 to 08:00 am it would be 22:00 to 32:00, on a time as 00:00 as 12:00 am or 24:00.
 

uk-rob

New Member
Joined
Apr 11, 2009
Messages
6
Thanks. I have no issues with the formatting of the time, just the calculation of it between two points using the variables. Whilst 12 noon is technically PM, I need to encompass all work carried out up until 1230 as AM.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Perhaps the following may work for you ?

Excel Workbook
ABCDEF
10000-05590600-12291230-18291830-2359
2StartEndOvernightAMPMEvening
306:0014:000.006.501.500.00
411:0019:000.001.506.000.50
522:0008:0010.000.000.000.00
Sheet1
 

uk-rob

New Member
Joined
Apr 11, 2009
Messages
6

ADVERTISEMENT

DonkeyOte,

That is fantastic...thank you very much...just exactly what I needed and about 10 arguments less than I originally had!

Many, many thanks.

Rob.
 

uk-rob

New Member
Joined
Apr 11, 2009
Messages
6
DonkeyOte,

How do I handle it functionally when the start time is, say 14:00? I get a negative in the 0600-1230 column and a corresponding postive in the 1830-0000 column.

Been trying a few things, none of which I can get to yield a satisfactory result. Any help would be appreciated.

Thanks again,

Rob.
 

Diablo II

Well-known Member
Joined
Sep 28, 2008
Messages
538

ADVERTISEMENT

if start time was 14:00 what was end time?
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Apologies, oversight on my part... the simplest solution is to encase the result within a MAX test (0, result) ... thus if result is -ve 0 is returned.

Excel Workbook
ABCDEF
10000-05590600-12291230-18291830-2359
2StartEndOvernightAMPMEvening
306:0014:0006.51.50
411:0019:0001.560.5
522:0008:0010000
614:0023:00004.54.5
Sheet1


I also added a ROUND function.
 

uk-rob

New Member
Joined
Apr 11, 2009
Messages
6
Thanks for taking the time and trouble to help me mate, all working very well.

Much appreciated! :biggrin:

Rob.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,898
Messages
5,627,530
Members
416,250
Latest member
darius_rebelo

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