Calculating Hours Worked

bootj1234

Board Regular
Joined
Aug 27, 2012
Messages
85
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.

2lazcqc.jpg
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
^^ was about to post the same :)

Code:
=IFERROR(IF(ISNUMBER(FIND("EVM",Q3)),(TIMEVALUE(SUBSTITUTE(C3,"<=","",1))-TIMEVALUE(SUBSTITUTE(B3,">=","",1)))*24,""),"")
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
So if you find MID($Q$3,16,3) in any cell in range $B$7:$B$5379, you want to calc time - time?
 
Upvote 0
Try

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

Thanks for all your support.

gaz_chops, your last formula refinements are working for me!

Best regards
 
Upvote 0

Forum statistics

Threads
1,223,714
Messages
6,174,052
Members
452,542
Latest member
Bricklin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top