Calculating time blocks

[uk-rob]

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.

 

[Diablo II]

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]

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]

Perhaps the following may work for you ?

Excel Workbook
	A	B	C	D	E	F
1			0000-0559	0600-1229	1230-1829	1830-2359
2	Start	End	Overnight	AM	PM	Evening
3	06:00	14:00	0.00	6.50	1.50	0.00
4	11:00	19:00	0.00	1.50	6.00	0.50
5	22:00	08:00	10.00	0.00	0.00	0.00
Sheet1

 

[uk-rob]

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]

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]

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

 

[uk-rob]

Hi. Could be anytime before 23:59.

Rob.

 

[DonkeyOte]

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
	A	B	C	D	E	F
1			0000-0559	0600-1229	1230-1829	1830-2359
2	Start	End	Overnight	AM	PM	Evening
3	06:00	14:00	0	6.5	1.5	0
4	11:00	19:00	0	1.5	6	0.5
5	22:00	08:00	10	0	0	0
6	14:00	23:00	0	0	4.5	4.5
Sheet1

I also added a ROUND function.

 

[uk-rob]

Thanks for taking the time and trouble to help me mate, all working very well.

Much appreciated!

Rob.