Time Values in Excel

[davidbeach]

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.

 

[IRstuff]

No, but other than the annoyance factor, is there a problem?

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[davidbeach]

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.

 

[IRstuff]

Well, one option might be to have a duplicate cell that displays the time value in the serial number format, i.e., ddddd.pppppppppp

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[cherrypicker]

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

Ian

 

[davidbeach]

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.

 

[IRstuff]

OK, agreed, that's bleeding annoying. One option is to build the time directly with the timecode format and reference the result in another cell with hour format, i.e.:
A1 = B1+C1/86400000
B1 = hh:mm:ss
C = ms

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[davidbeach]

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.

 

[IRstuff]

You could encapsulate this into a VBA function. I don't have sufficient insight into your operational scenario to suggest anything better.

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[flash3780]

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)

 

[IRstuff]

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
Chinese prisoner wins Nobel Peace Prize

 

[IDS]

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/