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 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
ignatzatsonic

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
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 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
-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 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 jumps after correct setting of date/time/timezone?
Have set the correct time zone, set the date and time, and within half an hour, the time jumps ahead significantly. Time then continues jumping periodically, and date as well. Any patch available for this? Thanks.
General Discussion
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
Can't open 2003 Excel Files in Excel 2007?
I'm not sure if it because the files are 2003 or not? When I try to open them an error message appears stating, "...the file you are trying to open is in a different format than specified by the file extension. Verify file is not corrupted and is from a trusted source..." These are my files. The...
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 00:06.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App