If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
time differences in a column
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) |
#2
|
|||
|
|||
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 "68magnolia71" wrote in message news 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) |
#3
|
|||
|
|||
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" wrote: 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 "68magnolia71" wrote in message news 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) |
#4
|
|||
|
|||
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:A10 00,FALSE))-B1,"") HTH, Bernie MS Excel MVP "68magnolia71" wrote in message ... 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" wrote: 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="St art",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 "68magnolia71" wrote in message news 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) |
#5
|
|||
|
|||
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" wrote: 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:A10 00,FALSE))-B1,"") HTH, Bernie MS Excel MVP "68magnolia71" wrote in message ... 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" wrote: 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="St art",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 "68magnolia71" wrote in message news 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) |
#6
|
|||
|
|||
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 "68magnolia71" wrote in message ... 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" wrote: 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:A10 00,FALSE))-B1,"") HTH, Bernie MS Excel MVP "68magnolia71" wrote in message ... 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" wrote: 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="St art",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 "68magnolia71" wrote in message news 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) |
#7
|
|||
|
|||
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" wrote: 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 "68magnolia71" wrote in message ... 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" wrote: 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:A10 00,FALSE))-B1,"") HTH, Bernie MS Excel MVP "68magnolia71" wrote in message ... 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" wrote: 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="St art",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 "68magnolia71" wrote in message news 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) |
#8
|
|||
|
|||
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 "68magnolia71" wrote in message ... 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" wrote: 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 "68magnolia71" wrote in message ... 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" wrote: 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:A10 00,FALSE))-B1,"") HTH, Bernie MS Excel MVP "68magnolia71" wrote in message ... 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" wrote: 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="St art",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 "68magnolia71" wrote in message news 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) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calendar Question | Josh | General Discussion | 7 | March 28th, 2005 11:19 PM |
how do I convert a date and time column to a time column | thdorsky2 | Worksheet Functions | 1 | March 4th, 2005 08:49 PM |
Using Validation to force entry into cells? | Mark | General Discussion | 16 | October 27th, 2004 09:23 PM |
Outlook 2003 Terminal Server Time Zone issue | Robert Strom | Calendar | 2 | May 26th, 2004 10:50 PM |
Calculating (Date and Time) differences | Frank Kabel | Worksheet Functions | 2 | April 27th, 2004 11:19 PM |