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.
Is there any way to change this behavior? I'm using excel 2002 SP3.





RE: Time Values in Excel
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: Time Values in Excel
RE: Time Values in Excel
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: Time Values in Excel
Ian
RE: Time Values in Excel
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
A1 = B1+C1/86400000
B1 = hh:mm:ss
C = ms
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: Time Values in Excel
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
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: Time Values in Excel
(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
=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
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/