Splitting a number for normal and overtime breakdown

[Soper87]

Hi,

I am sure to have done this before but racking my brain has led me to actually take the plunge into the forum to get help.

In creating a spreadsheet to simplify some tasks, I have managed to create it such that it will return the total number of hours required to complete a work package based on the options chosen. In this example it returns a total of 24 hours are required to do a specific job.

A standard working day is 10 hours consisting of 8 hours at normal working time and 2 hours overtime.

I need to be able to break the total hours down (24) to show the total Normal Time (NT) and Over Time (OT) hours required for this task. I would show these in two columns titled as such. In essence for a 24 hour job, the breakdown should be 20 hours NT and 4 hours OT (8;2 8;2 and then 4)

Is there a means to do this formula based or is it best set-up with VBA?

Thanks for any assistance!

 

[Dave Patton]

Excel 2010
	A	B	C	D
1	Normal Time	8
2	Overtime	2
3	Day	10
4
5			NT	OT
6	Project	24	20	4
5c

Cell Formulas
Range	Formula
C6	=INT(B6/10)*8+MIN(8,MOD(B6/10,1)*10)
D6	=B6-C6

 

[steve the fish]

Hi. You could try:

=FLOOR(A1,10)/10*8+MOD(A1,10)

for the normal time. The OT will just be the difference.

 

[Dave Patton]

"
=FLOOR(A1,10)/10*8+MOD(A1,10)

for the normal time. The OT will just be the difference."

Does this work if the project is 29 hours?

 

[steve the fish]

"
=FLOOR(A1,10)/10*8+MOD(A1,10)

for the normal time. The OT will just be the difference."

Does this work if the project is 29 hours?

Produces 25 which is correct as i understand it.

 

[steve the fish]

"
=FLOOR(A1,10)/10*8+MOD(A1,10)

for the normal time. The OT will just be the difference."

Does this work if the project is 29 hours?

Oh yes you are right. That should be 24.

 

[steve the fish]

This is better now:

=MIN(MOD(A1,10),8)+FLOOR(A1,10)*0.8

 

[Soper87]

Thanks for the quick responses and solutions! Much appreciated