Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Excel 2007: a question about date and time manipulation

18 Mar 2011   #1

Home Premium 64 bit
 
 
Excel 2007: a question about date and time manipulation

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 ?


My System SpecsSystem Spec
.

18 Mar 2011   #2

Microsoft Community Contributor Award Recipient

Windows 7 SP1, Home Premium, 64-bit
 
 

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.
My System SpecsSystem Spec
18 Mar 2011   #3

Microsoft Community Contributor Award Recipient

Windows 7 SP1, Home Premium, 64-bit
 
 

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

http://ittrainingtips.iu.edu/excel/c...-excel/04/2009
My System SpecsSystem Spec
.


19 Mar 2011   #4

Home Premium 64 bit
 
 

Re first reply
Code:
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
My System SpecsSystem Spec
19 Mar 2011   #5

Microsoft Community Contributor Award Recipient

Windows 7 SP1, Home Premium, 64-bit
 
 

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.


Attached Thumbnails
Excel 2007: a question about date and time manipulation-untitled-1.jpg  
My System SpecsSystem Spec
Reply

 Excel 2007: a question about date and time manipulation




Thread Tools



Similar help and support threads for2: Excel 2007: a question about date and time manipulation
Thread Forum
Excel 2007 Date Factor Microsoft Office
Separating date and time in Excel Office 2007 Microsoft Office
Time and Date issue - BIOS time and date reset after change in Win7 General Discussion
Solved Date/Time Question Customization
Solved Excel 2010 question: Date 00-00-0000 (08-03-2012)(dd-mm-yyyy) Microsoft Office
Date questions...Excel 2007 Microsoft Office
Excel and MS-Money 2005 date and time format display issue Microsoft Office

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 01:38 AM.
Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App
  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33