# calculate durations and skip blank cells

#### shahrinac

##### New Member
Hi,

I need to calculate durations of certain events and have Excel skip blank cells. I have timestamps in one column, col A (with every row filled in) and in the next column, col B, I have certain events associated with the timestamps. However, the problem is that I don't have every row filled in in the events column. I can't simply sort these data and remove blank rows in the events column because I have other data in the next columns (columns C onwards) that are associated with the timestamps that I need to leave in the Excel sheet. For example, the data look something like this

Time Event
0:17 event A
0:24
1:19
1:31 event B
2:14
3:36 event C

I want to be able to calculate the duration of time for each of the events - how long did event A last, how long did event B last? So I need to subtract the time 1:31 from 0:17 to calculate duration of event A, and so on. But I need to tell Excel that it needs to skip all the blank cells in column B and whenever it finds a cell filled in, it should subtract the time associated with that (in the column to the left of that cell) and subtract that from the time in the original cell. Is there any way to use a collection of formulas to do this in Excel?

Many thanks in advance for any help provided!

SC

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
welcome, are u looking for this

<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">{=IF(<font color="Blue">B2="","",MOD(<font color="Red">INDEX(<font color="Green">A3:A\$7,MATCH(<font color="Purple">TRUE,B3:B\$7<>"",0</font>)</font>)-A2,1</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br />

Excel 2013
ABC
1TimeEvent
200:17event A01:14
300:24
401:19
501:31event B02:05
602:14
703:36event C
Sheet1

Thank you very much! This seems to be working! Very very grateful for this.

Replies
3
Views
302
Replies
1
Views
2K
Replies
0
Views
265
Replies
4
Views
455
Replies
1
Views
169

Threads
1,196,373
Messages
6,014,897
Members
441,854
Latest member
Amstaff

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

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