I have pasted data in to Excel that is in two columns
Col A contains a list of dates, dd/mm/yy; col B times hh/mm/yy. How do I set about calculating and displaying the period in dd/hh/mm/ss that elapsed between col A row B with col B row B, and col A row C with col B row C ?
Post a picture of a sample worksheet containing sample data so I can fully understand what you need to do.
I will replicate the picture you post and can probably figure it out.
As I understand it, column A contains only something like 13/6/2007 (June 13, 2007). Column B contains hours I guess, but I need a sample to actually look at and ponder.
You want to calculate the total time elapsed between some time on June 13, 2007 in column A and some later time as shown in column B??
Column A contains no reference to hours? Hours are found only in column B?
More info please.
Here is an excellent explanation of how to do it, depending on your exact requirements:
http://ittrainingtips.iu.edu/excel/c...-excel/04/2009
Re first reply
Time elapsed between data in rows 5 and 18 is neededCode:Row Col A Col B 5 21/02/11 23:58:02 18 17/03/11 00:00:33
Last edited by rundwald; 19 Mar 2011 at 04:10.
Your example is complicated by 2 factors:
1: Using separate columns for date and time, rather than a single column
2: Using British format for the date
I have never tried to calculate elapsed time in Excel, but spent the last 2 hours learning. The following is the best I can do in the allotted time.
The pic below shows your chosen dates and times, but I have converted them to US date format: month/day/year rather than the British day/month/year. Additionally, I have put the time and date in the same cell, not in separate columns.
The formula in cell B4 is =a4-a3.
The formatting in column A is set to US English. The type is set to display 3/14/01 13:30.
The formatting in cell B4 where the formula resides is set to custom [h]:mm:ss. You can play around with that too.
The answer is 552 hours, 2 minutes, 31 seconds.
Note that the seconds are not displayed in cell a3, but are shown in the formula box at the top. The formula box also shows the time in traditional AM/PM format.
To use my method, you will have to convert your data to a single column, with date and time in the same cell, just as my sample shows. And you will have to use a US date format.
That's the best I can do. You can fiddle with the formatting and formulas and possibly come up with an improvement. Maybe someone else can add to this.
Quote