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

#### Snowbumms

##### New Member
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 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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### DonkeyOte

##### MrExcel MVP
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
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
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

Example:

Cell F...............Cell AV......................Cell BD

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

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

#### Snowbumms

##### New Member
Will it post in a private message?

Replies
0
Views
1K
Replies
4
Views
249
Replies
0
Views
185
Replies
9
Views
459
Replies
3
Views
772

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,951
Messages
5,834,531
Members
430,295
Latest member
amdis

### 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.

### Which adblocker are you using?

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

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