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!!!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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.
 

Snowbumms

New Member
Joined
Jul 9, 2008
Messages
48
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!
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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:

Snowbumms

New Member
Joined
Jul 9, 2008
Messages
48

ADVERTISEMENT

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.
 

Snowbumms

New Member
Joined
Jul 9, 2008
Messages
48
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.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

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)
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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:

Snowbumms

New Member
Joined
Jul 9, 2008
Messages
48
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,137
Messages
5,599,945
Members
414,350
Latest member
ajesh27

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
Top