Calculating Hours Worked

[bootj1234]

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.

 

[JumboCactuar]

I need help with determining a formula

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

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

 

[bootj1234]

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

Thanks for the quick response and lesson!

Kindest regards - John

 

[gaz_chops]

Or

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

 

[bootj1234]

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

 

[gaz_chops]

Maybe

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

 

[bootj1234]

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

 

[bootj1234]

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

 

[JumboCactuar]

Can't test this ATM but something like:

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

 

[bootj1234]

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.