time differences in a column

[Guest]

Need to calculate the difference between two cells in a column. Exemple

A B C
Start 1 date/time
open date/time
: date/time
: date/time
: :
Start2 date/time =date2/time2 - date1/time1
(HH:MM)

I can select two consecutive "Start cells" with "IF"s, but the max of "IF"
supported by Excel is 7. I have no solution if there are more than 7 lines
between tho consecutive "start"s (or any other, like "open" etc..)

I'd be glad to get some help.

68magnolia71 (in france)

 

[Bernie Deitrick]

68magnolia71,

Assumptions: only two data points, and the data you are looking for is
marked by the word "Start" in column A.

Array enter, using Ctrl-Shift-Enter:

=INDEX(B:B,MAX((A1:A200="Start")*ROW(A1:A200)))-INDEX(B:B,MIN(IF(A1:A200="Start",ROW(A1:A200),1000)))

All on one line. Adjust the A1:A200 to suit your actual range. Increase the
1000 if your data extends beyond row 999.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Helo Bernie,

I used the function you've sent me but unfortunetely it didn't work. To be
more precise, in Column "A" the word "Start" appears every so often but two
successive "Start"'s are separated by more than 7 rows (lines?). I need hte
difference between two successive "Start"'s. I believe a macro could do but
it is beyond my knowledge.

Anyway many thanks for the job you did.

68magnolia71

 

[Bernie Deitrick]

68magnolia71,

Then use this in C1, and copy down to match your data in columns A and B:

=IF(A1="Start",INDEX(B2:B1000,MATCH("Start",A2:A1000,FALSE))-B1,"")

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thank you Bernie, I'll try it as soon as the word "Match" is tranlated. It's
not a problem in plaln english, but in Excel french I don't know. Yet. Never
mind.

Other problem that has just arisen:

You may place a text in a cell and if its length exeeds the cell's capacity
you could make what's needed in the menu "Format Cell" and have the height
adjusted to the text.
But it doesn't work if two or more celles are "FUSED" (Sorry I don't the
actual word in Excel), the height of the row remains as you've set it. Is
there a way to make it work?


magnolia71 (like Magnolia in AK)

 

[Bernie Deitrick]

magnolia71,

The word is "Merged", and you need to use a macro - see below, written by
Jim Rech.

To translate, try this little su, which will work since VBA is only in
English, but Excel does the translation needed. Just select a blank cell,
and look at the resulting formula after you run the macro.

Sub PutMatch()
ActiveCell.Formula = "=MATCH(A1,B1:B2,False)"
End Sub

HTH,
Bernie
MS Excel MVP

Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .Cells(1).WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + _
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If

End Sub

 

[Guest]

Hello Bernie,

I'm convinced that the macro you posted is working properly but I just do no
know how to "insert" it in my worksheet. Then do I have to run this macro for
each cell I need to merge? No other way than a macro? See spreadsheet:
A B C
1 Data 1 Data 2 Data 3
2 Comments 1:
3 Comments 2:
4 Comments 3:

For ex. A4 B4 C4 are merged as A2 B2 C2 and A3 B3 C3 , A1, A2 & A" are
single cells.


The other function (=IF(A1="Start",INDEX etc..) is doing fine but I wasn't
able to use the macro. I had choose in selected number of functions. I'm
standing in front of a macro like a hen finding a comb ! I guess I will have
to learn the basics quite soon.

Thank you very much indeed

magnolia71

 

[Bernie Deitrick]

68magnolia71,

Copy the code into a codemodule in your workbook. Here's a good tutorial on
getting started with macros:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

The macro as written needs to be run for each merged cell, but a master
macro can be written to run it on every merged cell in a worksheet,
workbook, or every Excel file you have. You just need to specify exactly
how/what....

HTH,
Bernie
MS Excel MVP