# Combining two formulas?

#### SPERANZA

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

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Simple example
Book1
ABCD
1DateHrsWorkedRegOT
21/25/2005770
Sheet1

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

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.

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.

Replies
6
Views
139
Replies
1
Views
137
Replies
3
Views
197
Replies
2
Views
270
Replies
0
Views
121

1,203,066
Messages
6,053,330
Members
444,654
Latest member
Rich Cohen

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

### Which adblocker are you using?

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

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