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
rundwald

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
ignatzatsonic

Microsoft Community Contributor Award Recipient

Windows 7 Home Premium SP1, 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
ignatzatsonic

Microsoft Community Contributor Award Recipient

Windows 7 Home Premium SP1, 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
rundwald

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
ignatzatsonic

Microsoft Community Contributor Award Recipient

Windows 7 Home Premium SP1, 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
Thread Forum
Excel 2007 Files Are Now Saving with Fewer KB in Both Excel 2007 and 2
The “problem” I’m concerned about is that I noticed existingExcel 2007 xlsx files are now saving with fewer Kb. I have a Lenovo W700ds and a W541. Both computers are running Windows 7 Pro 64bit. The W700 has Office 2007 and theW541 has Office 2013. Both versions ofExcel are 32 bit as far as I...
Microsoft Office
Excel 2007 Date Factor
I have a spreadsheet of inventory items and the dates are in various formats Europe , USA , and Eastern. Example below. What I need to do is select all the dates and covert into one recognizable formula Month (1) Day (1) Year (****). Let me know how to do this. Thanks! 7/12/00 28/12/00...
Microsoft Office
Separating date and time in Excel Office 2007
I have Office 2007 running on a Windows 7 64 bit Toshiba laptop. I am trying to separate the data and time in an Excel file in one column and put the date in one column and the time in another. I need a "how to" procedure to do that.
Microsoft Office
Date/Time Question
Was looking at some forums last week and noticed on one of them that the user's date/time was showing a different color from the default white. His showed a yellow/gold color in the system tray. Just curious if there is a way to change the default white to another color in Win7?
Customization
Excel 2010 question: Date 00-00-0000 (08-03-2012)(dd-mm-yyyy)
Yo folks, Annoying little thing in excel, I want to show the date exactly like this: 00-00-0000 (08-03-2012)(dd-mm-yyyy) Instead excel removes the zero's like this: 0-0-0000 (8-3-2012) (d-m-yyyy) Is it possible to keep the zero's? Thanks in advanced,
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 20:48.
Twitter Facebook Google+ Seven Forums iOS App Seven Forums Android App