Time and Date Format in Excel 2007

Microsoft Office excel 2007 home & stude...
July 8, 2010 at 13:47:30
Specs: Windows XP
 My DAQ system outputs the time and date for each data point collected in the first column along with the various signals in other columns, and I have at least 4k data points in each column. Time and data is given as shown below.11:54:23:11 - 23 MAY 1011:54:38:11 - 23 MAY 10I am trying to extract the time diference between the two data points by subracting the info in two cells, and obviously Excel does not recognize the info in the cell because of the presence of ":11" after hh:mm:ss info.How would I delete ":11" from the column and convert the given info to a Excel readable time/date format so that info in two cells could be subtracted to get the time difference.Thanks

See More: Time and Date Format in Excel 2007

#1
July 8, 2010 at 14:15:34
 Are the Time & Date together in one cell?If the times are in there own cells, then Custom Format as:[h]:mm:ss.00This will allow you to keep the full time.MIKEhttp://www.skeptic.com/

Report •

#2
July 8, 2010 at 14:29:57
 Hi,If the time and date are in one cell exactly as shown in your post, then this formula will convert a value in cell A1 to an Excel Date/time format:=DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" - ",A1)-2)) + TIMEVALUE(LEFT(A1,8))Using the above formula on the two time/date strings allowed a simple subtraction to work - yielding 15 seconds in a cell formatted hh:mm:ssRegards

Report •

#3
July 9, 2010 at 06:54:58
 Mike, All the TIME and DATE are in one cell.Humar, THANKS a lot, your function works great. Not having used RIGHT, LEFT, LEN and FIND functions before. It took a while to understand how you were getting the time difference between two data points. Once understand, I simplified the function furter11:54:23:11 - 23 MAY 1011:54:38:11 - 23 MAY 10=DATEVALUE(RIGHT(A1,9)) + TIMEVALUE(LEFT(A1,8)Again Mike and Humar thank you much, it was very helpful.

Report •

Related Solutions

#4
July 9, 2010 at 07:09:50
 Hi,Glad to hear it worked.The reason I used the Find function for the '-' before the date was because I didn't know if the month was always three characters.Your version should work just fine with the data as long as the text for the month stays at three characters .... JANUARY would really mess it up, but JAN would of course work.Regards

Report •

#5
July 14, 2010 at 06:27:25
 HumarIf two cells contain the time and date info in hh:mm:ss d-mmm-yy format, as shown below12:36:40 30-Jun-1012:59:31 2-Jul-10How could info in cell two be subtracted from cell one to obtain the number of "Seconds" between the two events as an integer number. Thanks for all your help.RegardsThanks, after taking your original help few days ago, I was able to solve the date/time issue my self. Thanks, at this I am set and I may need your help if I have additional questions.Regards,Re

Report •

#6
February 4, 2011 at 06:54:04
 how can you format time 22:32:03to 1/4/1900 10:07:18 AM

Report •