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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
this is a little vague I'm afraid -- we'd like some info with some illustrations of the data and what the results should be (and where they should go)... also what happens if BD > AV ??

anyway, this would sum the time differences, it is an Array formula and must be committed using SHIFT+CTRL+ENTER

=SUM(IF(UPPER($F$7:$F$500)="CLEARDALE",$AV$7:$AV$500-$BD$7:$BD$500))*24

Once array has been committed it will appear as:

{=SUM(IF(....)}

The answer will be in decimals (ie 19.50 = 19hrs 30mins) if you prefer time format remove the *24 and format cell as [h]:mm:ss

If this is not what you need please post back with as much detail as possible so the forum can assist.
 
Upvote 0
Ok here goes,

Cell F Cell AV Cell BD
Station Name Alert Time Available Time

Cleardale 00:43:52 01:52:30
Monkton 03:52:57 06:00:51
Newyork 09:05:23 09:35:21
Miami 15:22:49 18:32:00
Cleardale 07:32:32 08:34:54

What I am trying to so is create a formula to pickup the data each time I enter the name such as Cleardale. When it does I want it to minus the Available time from the Alert time to get a time on task. When the time on task is calculated I then want it to add the time from another calculation of Cleardale Available time minus the alert time so when I am done I get a summation of the time total of Cleardale on a task.

I have been scratching my head for days on this and come here in desperation and in search of assistance from the Excel jedi masters!! Thank you!
 
Upvote 0
have you tried the solution provided ? this would give total time taken for given station to clear alerts.

EDIT: my earlier formula had times incorrectly ordered times given latest post (AV-BD rather than BD-AV)...
try below -- commit as array (SHIFT+CTRL+ENTER)

=SUM(IF(UPPER($F$7:$F$500)="CLEARDALE",$BD$7:$BD$500-$AV$7:$AV$500))*24

to show as hour:mins rather than mins as decimal remove the *24 and format as [h]:mm:ss
 
Last edited:
Upvote 0
Example:

Cell F...............Cell AV......................Cell BD
Station.............Alerttime...................Availabletime
Cleardale...........11:40:00..................13:20:00 (time on task01:40:00)
NewYork............09:30:00..................10:00:00 (time on task00:30:00)
Cleardale...........23:50:00...................00:10:00 (time on task00:20:00)


So I need the formula to Find cleardale and then take BD time and subtract AV time from it to produce time on task. I then need time on task added for Cleardale.
 
Upvote 0
It doesn't work it for some reason it doesnt produce a result. Do I need to add a if <1*24? I just cant figure it out.
 
Upvote 0
OK can you use BE on every row ?

=BDx-AVx+(1*(BDx<AVx))

where x is row number ?

then you can simply use a SUMIF utilising BE values.

=SUMIF($F$7:$F$500,"CLEARDALE",$BE$7:$BE$500)
 
Upvote 0
format is screwy now on this post..

yes you need to adjust BD if BD less than BD

ie BDx - AVx + 1 where BD < AVx

(my formula won't display)

or using array (ie not using AE)... hoping this posts... :(

=SUM(IF($F$7:$F$500="CLEARDALE",$BD$7:$BD$500-$AV$7:$AV$500+IF($BD$7:$BD$500<$AV$7:$AV$500,1,0)))
 
Last edited:
Upvote 0
Oh and its on sheet 1-sheet1! when the results are on sheet2, did it maybe error the formula when I added that into it and then added it as an array?
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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