# Calculating Hours Worked

#### bootj1234

##### Board Regular
I need help with determining a formula that would count total hours base on not looking at the “>=” in cells B3 and B4, and not looking at “<=” in cells C3 and C4.

Please see the following image for the outcome I am trying to achieve in cells D3 and D4

Thanks for the assistance.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
I need help with determining a formula

In D3, this assumes B column always contains >= and C always contains <=

Code:
``=IFERROR((TIMEVALUE(SUBSTITUTE(C3,"<=","",1))-TIMEVALUE(SUBSTITUTE(B3,">=","",1)))*24,"")``

Last edited:
This is working great! Your response uses Function combinations I have never used before.

Thanks for the quick response and lesson!

Kindest regards - John

Or

=IFERROR(TIMEVALUE(REPLACE(C1,1,2,""))-TIMEVALUE(REPLACE(B1,1,2,"")),"")

One more question regarding the above formulas: how would the formula need to be modified to calculate based on a first meeting a condition from another cell with the text EVM? For sake of outcome, lets say that EVM is referenced from cell F1.

Thanks

Maybe

=IF(F1="EVM",TIMEVALUE(REPLACE(C1,1,2,""))-TIMEVALUE(REPLACE(B1,1,2,"")),"")

How would I incorporate this code MID(\$Q\$3,16,3) into the formula to set a conditional by which
=IFERROR((TIMEVALUE(SUBSTITUTE(C3,"<=","",1))-TIMEVALUE(SUBSTITUTE(B3,">=","",1)))*24,"")can be executed to solve for the answer?

The code MID(\$Q\$3,16,3)is identifying EVM in a text string in order to let the rest of the formula calculate.

Thanks

How would I incorporate this code MID(\$Q\$3,16,3) into the formula to set a conditional by which
=IFERROR((TIMEVALUE(SUBSTITUTE(C3,"<=","",1))-TIMEVALUE(SUBSTITUTE(B3,">=","",1)))*24,"")can be executed to solve for the answer?

The code MID(\$Q\$3,16,3) is identifying EVM in a text string in order to let the rest of the formula calculate.

Thanks

Can't test this ATM but something like:

Code:
``=IFERROR(IF(MID(\$Q\$3,16,3)="EVM",TIMEVALUE(SUBSTITUTE(C3,"<=","",1))-TIMEVALUE(SUBSTITUTE(B3,">=","",1)))*24,"","")``

Thanks for the revised formula. When I apply it into my spreadsheet it doesn't calculate. The amount that it should provide in the way of a answer is 11.

If it helps, here is the text string in cell \$Q\$3 on my spreadsheet "EASTVIEW MALL: EVM ( 8 = Total Screens In Network )"

Thank you.

Replies
4
Views
115
Replies
10
Views
491
Replies
1
Views
83
Replies
1
Views
192
Replies
21
Views
729

1,196,073
Messages
6,013,269
Members
441,758
Latest member
Abren

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