Calculations in Time Problem

S

scottymelloty

im sure this is obvious but

say ive got 2 times
A1)1:32.101
A2)1:32.105

how can i do the maths A2-A1 etc , ive formatted everything t
mm:ss.000 as ive read that when searching on forum but when i put thi
in B1 =A2-A1 i just get #VALUE

how do i get it so it gives me the answer 0:00.004 in B1

any help would be much appreciated:
 
J

JE McGimpsey

Sounds like your values were entered as Text. Making sure that A1:A2 are
the correct format, then reentering the times should clear up the
problem.

scottymelloty
 
D

Dave Peterson

My guess is that one (maybe both) of your entries are non-numeric.

Format the cells and General and retype the entries:

00:01:32.101
and
00:01:32.105
 
S

scottymelloty

Thanks for the input guys,

i doubled checked and i had formatted everything right but ive jus
figured something out

when i go into tools- options - international, the use system seprator
option was ticked off, if i tick this on it works if i enter the time a
1:32,105 and i can do my calculation, thuis is fine but would prefer t
use . instead of , for the thousands separtor, ive tried changing the
to . manually when i unclick the use system seprators option but thi
dosnt wor
 
D

Dave Peterson

This is a windows setting--so it'll affect everything that uses a decimal point
(and 1000 separator).

windows start button|settings|control panel|regional and language Options
regional options tab|customize button|numbers tab
Change your decimal symbol to dot (.)
digit grouping symbol to comma (,) (I'm guessing)
and in the USA, most of us use a list separator of comma (,)
 
S

scottymelloty

Thanks Dave,
ive suspected the settings on this computer are set to USA rather than
UK but it is a works computer and they have taken things off to stop
things being changed and there is no option to change the regional
settings so i will look into it, i shall try on my computer at home
where im guessing i will be able to do maths with 1:32.105 etc without
any problems without having to use 1:32,105
thanks again
 
D

Dave Peterson

Well, if you're gonna experiment on any pc, write down the existing settings
before you change them.

If things don't go correctly, just change them back. (Don't rely on your memory
<vbg>.)
 
Top