Combining two formulas?

[SPERANZA]

I'm calculating a cell and then if the value is 8 or greater then 8 "appears" in the cell, but the true value is still there for calculation purposes. If it's less than 8 then the actual value appears in the cell. I then want to take the amount that is over 8 and put it into another cell for calculation purposes of overtime.

How do I create such a formula?

Thanks for any help offered

 

[ken2step]

Simple example

Book1
	A	B	C	D
1	Date	HrsWorked	Reg	OT
2	1/25/2005	7	7	0
Sheet1

 

[jindon]

Hi

not sure if you want this kind

select the cell(s) that you wnat to display 8 when its actual value exceed 8 then

go to Format Cells -> Custom then type

[>=8]8;-0;0;@

then press OK

displays 8 when the cell value is greater than 8, but still the real value is its actual value.

hope this helps

jindon

 

[SPERANZA]

Sorry for the incomplete description.
I have a timesheet that has military time format. I'm trying to calculate the total time worked per day and then add up the hours and determine the overtime.

Mon Tue Wed Thu Fri Sat Sun
800 800 815 830 800 900 900
1700 1700 1700 1640 1750 1720 1745
9 9 8:45 8:10 9:50 8:20 8:45

1 1 :45 :10 1:50 :20 :45

Then I need to have the cells that calculated to be over 8, just show an 8 and then several cells below that I have an OT row that will show the OT difference. I can't get the military times to calculate properly and then have the conditional formatting happening in the same cell as the calculation is occuring.

 

[rs2k]

Hello,
It's messy, but it works.
In A1 is Monday, B1 Tuesday etc...
In A2 Start time
In A3 Finish time
In A4 =A3-A2
In A5 08:00
In A6 =A4-$I$1 (I1 or $I$1 is where I put 08:00 daily hours, you could use any spare cell and reference it)
Copy across to fill your week in.
Then I hid row four. This leaves you with your start time finished time, total time (hidden) and the overtime.
I used custom format hh:mm
I said it was messy, but it works.

Colin.