Count IF and IF then Subtract two times in 24 hr Format to recieve time on task

Snowbumms

New Member
Joined
Jul 9, 2008
Messages
48
Hello Excel Masters!

I am totally out of my league but I have been doing my best with creating an excel formula but have miserably failed up to this point.

Here is what I am trying to do:

I get a spreadsheet downloaded to excel with a variable of 15 names.
I want to create a formula that if the name Cleardale appears to take the time responding in cell BD and subtract it from the time available in cell AV so that I have the time on task and then to add up all those times in a total time on task for all cleardale listings for that day. The times are listed in the 24 hour format. I keep getting errors. Can anyone help me out on this one? The range of the cells is from AV7 to AV500 for available time and BD7 to BD500 for alert time and the range of the names is cell f7 to f500. Please help!!!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
OK last one -- I am going to stop editing my posts :)

Assuming data is on sheet1

=SUM(IF('Sheet1'!$F$7:$F$500="CLEARDALE",'Sheet1'!$BD$7:$BD$500-'Sheet'1!$AV$7:$AV$500+IF('Sheet1'!$BD$7:$BD$500<'Sheet1'!$AV$7:$AV$500,1,0)))

Try that (as array...)
 
Upvote 0
Will it process each line and then go to the next and add it if clear dale appears or will it take the total from BD and subtract it from AV and then add it? I am getting a error message. I really really really appreciate your help and posts by the way your amazing!!
 
Upvote 0
what it's doing is going through each row in the ranges and doing the calculation and then summing up the results in one go... so it will only calculate BD-AV in any given row if in that same row value in F = cleardale...

there is a typo in my formula, misplaced apostrophe (Sheet'1 rather than Sheet1'), try the below (as array)

=SUM(IF('Sheet1'!$F$7:$F$500="CLEARDALE",'Sheet1'!$BD$7:$BD$500-'Sheet1'!$AV$7:$AV$500+IF('Sheet1'!$BD$7:$BD$500<'Sheet1'!$AV$7:$AV$500,1,0)))
 
Upvote 0
Simply amazing just amazing you rock! However, I seem to run into a wee roadbloack when the total time on task is over 24 hrs. I am going to try and work around this but I just want to thank you from the bottom of my heart!! Thank you very much!
 
Upvote 0
what is the issue when you exceed 24 hours ?

formatting the result cell as custom type: [h]:mm

would show 27 hours and 30 minutes as: 27:30

(if you wanted to show 27.5 simply add the *24 to the end of the formula)

Let me know the problem, am sure someone here can resolve for you.
 
Upvote 0
Hello again,
THe issue I now face is when the totals from the hours adds above 24 hours it simplyt goes back to zero instead of going to 25 hours 26 etc 40 etc.

Thank you. I am currently searching on how to fix this as I am sure it is not a huge fix. Thank you.
 
Upvote 0
=SUM(IF('Sheet1'!$F$7:$F$500="CLEARDALE",'Sheet1'!$BD$7:$BD$500-'Sheet1'!$AV$7:$AV$500+IF('Sheet1'!$BD$7:$BD$500<'Sheet1'!$AV$7:$AV$500,1,0)))

So when the total time for Cleardale is greater than 23:59:59 the value simple returns back to 0 and adds the time again up till 23:59:59 and repeats.


The Formula subtracts a time value in BD in the format of 24 hrs in h:m:s and subtracts it from a time value in AV of 24 hrs in h:m:s and gives a total time from cells 7-500 as you see. What I want to avoid is this reset back to zero but rather get a value to go say past 100 hrs.

Thank you. I am working on it too atm.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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