×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

Time Values in Excel

Time Values in Excel

Time Values in Excel

(OP)
I am working with times and doing time correction between different sources.  I can format cells to hold and display time as hh:mm:ss.000, but the formula bar only shows hh:mm:ss and if I click into the formula bar it causes the cell value to change to hh:mm:ss, throwing away the millisecond portion of the number.

Is there any way to change this behavior?  I'm using excel 2002 SP3.

RE: Time Values in Excel

(OP)
Probably mostly just the annoyance factor, but it would be nice to have excel work the way I want it to rather than the way someone at M$ programmed the default behavior, someone with no clue how the program is actually used.

RE: Time Values in Excel

Why can't you use the custom format "hh:mm:ss.000" to achieve your goal.

Ian

RE: Time Values in Excel

(OP)
I use the custom format hh:mm:ss.000 and that's what shows up in the cells.  What shows up in the formula bar, though, is only hh:mm:ss, and rounded at that.  Also, if I try to edit in the cell it changes to hh:mm:ss.  Not sure what one would do if it were necessary to have microseconds as excel won't take more than 3 zeros at the end of the format string.

The way it works now, if I want to edit any of the times I've entered, I have to retype the whole millisecond part, and as much as both digits of the seconds part.  Pain in the arse for no good reason.

RE: Time Values in Excel

(OP)
That could work, but when multiplied by a couple hundred could get to be just as obnoxious.

We had multiple storm related transmission events on Sunday.  Some of the substations don't have an IRIG source into the relays.  Where there was IRIG, some locations didn't switch to daylight saving time, and even with IRIG there can be millisecond offsets between different relays.  So I have 5-30 times from about 15 relays that I want to enter and time correct to line up everything that happened.  excel could make the process a whole lot easier.

RE: Time Values in Excel

Can you lock the cell to keep the format from changing? Does "Edit/Copy/Paste Special/Format Only" work?

(I'd check for myself, but the computer that I'm working on at the moment doesn't have Excel installed on it)

RE: Time Values in Excel

My bad, I didn't read the rest of your posting.  I would suggest that you parse the IRIG time at the decimal point, i.e., put the hh:mm:ss into one cell, and then the portion after the decimal point into an adjacent cell.  The third cell would then be:
=A1+B1/86400000

This would allow you to edit the time directly in columns A or B, and resultant time would be in column C.  This would only require copying and pasting the equation down into column C.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize

RE: Time Values in Excel

I can confirm that:

1. Excel 2010 still does the same thing
2. A quick search found a report of this problem back in 2004, but no solution, and as far as I could see Microsoft don't seem to recognise it as a problem.
3. I couldn't find anything useful in the options, or any way of protecting the format but not the contents.

My best offerings of a solution are:

1. If you didn't change anything in the cell you can get the format back by pressing undo (ctrl-z).  If you did make changes you will lose them if you do this of course.
2. Format the adjacent column the way you want, then you can copy, paste-special-formats to get the format back.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

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!


Resources