Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Time formatting in Excel

Time formatting in Excel

Time formatting in Excel

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?

RE: Time formatting in Excel

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


RE: Time formatting in Excel

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?

RE: Time formatting in Excel

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.

RE: Time formatting in Excel

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


RE: Time formatting in Excel

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

RE: Time formatting in Excel

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close