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.

 

[gaz_chops]

Try

=IF(ISNUMBER(FIND("EVM",Q3)),TIMEVALUE(REPLACE(C1,1,2,""))-TIMEVALUE(REPLACE(B1,1,2,"")),"")

 

[JumboCactuar]

^^ was about to post the same

=IFERROR(IF(ISNUMBER(FIND("EVM",Q3)),(TIMEVALUE(SUBSTITUTE(C3,"<=","",1))-TIMEVALUE(SUBSTITUTE(B3,">=","",1)))*24,""),"")

 

[bootj1234]

Is there a way that the formula can be made to work with the (MID($Q$3,16,3) without the =”EVM” so that I can change the (text,start_num,num_chars) when I
reference text in other cells in order to establish a different condition to be meet for the rest of the formula to deliver the correct new result; i.e., (MID($X$3,20,3)?

I sure appreciate all the help!

 

[gaz_chops]

The Find fucnction will find the value wherever it appears in the text, you can place the word to search in another cell and reference that, like

=IFERROR(IF(FIND(F1,Q5),TIMEVALUE(REPLACE(C3,1,2,""))-TIMEVALUE(REPLACE(B3,1,2,"")),""),"")

I put EVM in cell F1 but you can put this, or any word, in any empty cell.

 

[bootj1234]

Hi gaz_chops and JumboCactaur
After taking time to think about our post exchange last week, I realized that what I needed to do was use what I think should be the COUNTIF function to set up the conditional by which the rest of the formula should calculate the correct answer. I tried modifying the following formula but it isn't working and I was wondering if you would please offer a suggestion for what I am doing wrong with how the formula should be written in order to deliver an answer?

=IFERROR(COUNTIFS($B$7:$B$5379,MID($Q$3,16,3),TIMEVALUE(SUBSTITUTE(C3,"<=","",1))-TIMEVALUE(SUBSTITUTE(B3,">=","",1)))*24,"","")

Thanks - John

 

[gaz_chops]

So if you find MID($Q$3,16,3) in any cell in range $B$7:$B$5379, you want to calc time - time?

 

[bootj1234]

Yes, thank you.

 

[gaz_chops]

Try

=IFERROR(IF(COUNTIFS($B$7:$B$5379,MID($Q$3,16,3)),TIMEVALUE(SUBSTITUTE(C3,"<=","",1))-TIMEVALUE(SUBSTITUTE(B3,">=","",1))*24,""),"")

 

[bootj1234]

Hi gaz_chops and JumboCactaur,

Thanks for all your support.

gaz_chops, your last formula refinements are working for me!

Best regards

 

[gaz_chops]

You're welcome, glad we got it working.