Excel 2007: a question about date and time manipulation

[rundwald]

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 ?

 

[ignatzatsonic]

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.

 

[ignatzatsonic]

Here is an excellent explanation of how to do it, depending on your exact requirements:

http://ittrainingtips.iu.edu/excel/calculating-elapsed-time-in-excel/04/2009

 

[rundwald]

Re first reply

Row        Col A        Col B

  5         21/02/11     23:58:02
 18         17/03/11    00:00:33

Time elapsed between data in rows 5 and 18 is needed

 

[ignatzatsonic]

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.