A very Strange ":"

[cmwcg]

Dear All,
my friend came across a very strange formula, when you imput in any cell=-" :", you get a number:416.666, anyone can tell me why?
hope some one can solve my issue.

best regards!

calvin

 

[Fluff]

If you take the 6.9444 & multiply by 60 you get 416.6667 or 4PM

 

[Marcelo Branco]

You are in Utah and I am in New Jersey, so I assume our regional settings are the same. When I enter your "formula"...

=-" :0"

it returns 0 for me. I am using XL2010 if that might make a difference. As for the OP's original question, like Fluff, I get a #VALUE ! error for what the OP posted in his original message.

=-" :0"
Excel 2010 returns 0

Excel 365 returns 6,944444 (comma as decimal separator)


M.

 

[DataBlake]

theres a 0.041666 rep. difference between each interval using =-" :#" for office 365



=-" :0" is 6.944444
=-" :1" is 6.902778
=-" :2" is 6.86111

and so on and so forth
i think excel is just doing math on the number you insert and has nothing to do with time at least.

p.s i also get #VALUE ! for OP's formula

 

[DanteAmor]

On Excel 2007

=-" :0" is 0.000694444
=-" :1" is -0.04097222

I do not see any relation

 

[DataBlake]

On Excel 2007
I do not see any relation

excel 365 users are experiencing the same. i think its just a version error treating something as a formula thats not supposed to be a formula because of the "-"
seems excel 2010 is the only one that handles the error correctly?

edit:
because if i do =" :0" its just a :0

 

[cmwcg]

thanks for your reply. it is blank sheet. i am from China. i find this happened in office 2010,2013,2016 and 365. we also guess it indicates a time and date, what confused us is why and how? and from your answers, it seems that it shows different number in different region. anyway thank you all for your help

 

[cmwcg]

thanks for your reply and the issue is like a puzzle and it makes us confused about this. and hope we can find out the why at last.

 

[cmwcg]

Dear Sir,
thanks for your reply and we also find out the calculation rules as indicated in the attached picture. we want to make out why it is like this. [TABLE="width: 560"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]what you enter[/TD]
[TD]what you get[/TD]
[TD]the rules we find[/TD]
[/TR]
[TR]
[TD]1)[/TD]
[TD]" :"[/TD]
[TD="align: right"]-416.6666667[/TD]
[TD]=-36000000/24/60/60[/TD]
[/TR]
[TR]
[TD]2)[/TD]
[TD]" :A"[/TD]
[TD="align: right"]-6.944444444[/TD]
[TD]=(A*3600-600000)/24/60/60[/TD]
[/TR]
[TR]
[TD]3)[/TD]
[TD]" :A.b"[/TD]
[TD="align: right"]0.000717593[/TD]
[TD]=(A*60+b)/24/60/60[/TD]
[/TR]
[TR]
[TD]4)[/TD]
[TD]" :A:C"[/TD]
[TD="align: right"]-0.030324074[/TD]
[TD]=(A*3600+C*60-10000)/24/60/60[/TD]
[/TR]
[TR]
[TD]5)[/TD]
[TD]" :A:B.C"[/TD]
[TD="align: right"]0.170208333[/TD]
[TD]=(A*3600+B*60+C)/24/60/60[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 560"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]what you enter[/TD]
[TD]what you get[/TD]
[TD]the rules we find[/TD]
[/TR]
[TR]
[TD]1)[/TD]
[TD]" :"[/TD]
[TD="align: right"]-416.6666667[/TD]
[TD]=-36000000/24/60/60[/TD]
[/TR]
[TR]
[TD]2)[/TD]
[TD]" :A"[/TD]
[TD="align: right"]-6.944444444[/TD]
[TD]=(A*3600-600000)/24/60/60[/TD]
[/TR]
[TR]
[TD]3)[/TD]
[TD]" :A.b"[/TD]
[TD="align: right"]0.000717593[/TD]
[TD]=(A*60+b)/24/60/60[/TD]
[/TR]
[TR]
[TD]4)[/TD]
[TD]" :A:C"[/TD]
[TD="align: right"]-0.030324074[/TD]
[TD]=(A*3600+C*60-10000)/24/60/60[/TD]
[/TR]
[TR]
[TD]5)[/TD]
[TD]" :A:B.C"[/TD]
[TD="align: right"]0.170208333[/TD]
[TD]=(A*3600+B*60+C)/24/60/60[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 560"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]what you enter[/TD]
[TD]what you get[/TD]
[TD]the rules we find[/TD]
[/TR]
[TR]
[TD]1)[/TD]
[TD]" :"[/TD]
[TD="align: right"]-416.6666667[/TD]
[TD]=-36000000/24/60/60[/TD]
[/TR]
[TR]
[TD]2)[/TD]
[TD]" :A"[/TD]
[TD="align: right"]-6.944444444[/TD]
[TD]=(A*3600-600000)/24/60/60[/TD]
[/TR]
[TR]
[TD]3)[/TD]
[TD]" :A.b"[/TD]
[TD="align: right"]0.000717593[/TD]
[TD]=(A*60+b)/24/60/60[/TD]
[/TR]
[TR]
[TD]4)[/TD]
[TD]" :A:C"[/TD]
[TD="align: right"]-0.030324074[/TD]
[TD]=(A*3600+C*60-10000)/24/60/60[/TD]
[/TR]
[TR]
[TD]5)[/TD]
[TD]" :A:B.C"[/TD]
[TD="align: right"]0.170208333[/TD]
[TD]=(A*3600+B*60+C)/24/60/60[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]