Time formatting in Excel 2

[dmpritch82]

My excel spreadsheet has data for every tenth of a second for about an hour and a half; around 45000 cells. The data that was given to me has a flaw; when the hour gets past 12:59:59am it jumps back to 12:00:00 am. I need the hour to read 01:00:00am so that I can subtract the time intervals correctly since the minutes and seconds are different for every reading. I have tried to format the cells to only read mm.ss.0 but excel still recognizes the hour. Is there any way to change the hour and not the minutes and seconds without having to manually do it 45000 times?

 

[IRstuff]

Since you don't really care about absolute time, why not simply replace the time with something that increments by 0.1 every cell?

TTFN

 

[dmpritch82]

The time is important....I was estimating that it increased every 0.1 of a second. The data is from strain gages on a wing of a plane, it triggers around 4 times a second but is sporadic. The data is grouped according to the increase in time, but the time clicks back an hour after one hour. I just want the data in minutes and seconds so that i can see the time interval properly and graph my data according to time. Perhaps there is a way to do this in visual basic....Any suggestions?

 

[bpeirson]

Does Excel insert a time value when data is received from the strain gauge or does the strain gauge send time information to Excel? If Excel is inserting the time you maybe able to store it as a serial number instead of formatting it as a time you can recognize.

Another option may be to reset the system time so that the clock starts at 1:00 am. This is assuming that you are using the system clock for reference and the same problem won't occur at 2:00 am.

 

[IRstuff]

Format the time stamps as time serial numbers

Copy them into another column

Replace the entries after the rollover with a reference to the copied serial numbers plus 1/24

TTFN

 

[dmpritch82]

Thank you for your help....that worked awesome. I appreciate it

 

[m777182]

I solved same problems (passing over 12 o'clock or passing midnight) by forming a variable that incorporated date$ and time$ so that actual time was an increasing function in all cases.
m777182