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 10
11:54:38:11 - 23 MAY 10

I 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.

See More: Time and Date Format in Excel 2007

Report •

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:


This will allow you to keep the full time.



Report •

July 8, 2010 at 14:29:57

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:


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:ss


Report •

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 furter

11:54:23:11 - 23 MAY 10
11:54:38:11 - 23 MAY 10


Again Mike and Humar thank you much, it was very helpful.

Report •

Related Solutions

July 9, 2010 at 07:09:50

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.


Report •

July 14, 2010 at 06:27:25

If two cells contain the time and date info in hh:mm:ss d-mmm-yy format, as shown below

12:36:40 30-Jun-10
12:59:31 2-Jul-10

How 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.


Thanks, 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.



Report •

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

Report •

Ask Question