# Calculating time blocks

#### uk-rob

##### New Member
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.

### 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
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
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
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

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

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

#### uk-rob

##### New Member
Hi. Could be anytime before 23:59.

Rob.

#### DonkeyOte

##### MrExcel MVP
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
Thanks for taking the time and trouble to help me mate, all working very well.

Much appreciated!

Rob.

Replies
6
Views
408
Replies
7
Views
138
Replies
6
Views
183
Replies
7
Views
134
Replies
2
Views
179

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?

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