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

 

[kweaver]

Can't duplicate this in 2010 or 365.

 

[Fluff]

I just get a Value error.

 

[Eric W]

What are your regional settings? The colon seems to indicate a time, like 9:45 or something. When Excel sees something in quotes, it assumes it's text. But if you do a mathematical operation on it, like the minus sign, it tries to handle it like a number. So if you have something that Excel stores as a number, but is formatted like something else, you get the number. For example:

=-"7/22/19"

returns -43668.

So based on your regional settings you might get something odd. When I enter this formula:

=-" :0"

I get a result of 6.944444. So it has something to do with a time, but I don't see where the number comes from. Yours is probably related.

 

[Rick Rothstein]

So based on your regional settings you might get something odd. When I enter this formula:

=-" :0"

I get a result of 6.944444.

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.

 

[Eric W]

I also get a #VALUE ! when I enter the OP's original formula. Given the colon, I experimented a bit and came up with the updated one. I'm using Excel 365, and other than that, I'd assume our regional settings are the same. I don't know why the version should do something different though. I wonder what version the OP is using.

 

[Fluff]

With Eric's modification I also get 6.9444444
Using 365 with UK settings

 

[Joe4]

Also try checking your Autocorrect entries to see if you have an entry for: -" :0"

I was able to add an Autocorrect entry for -" :0", telling it to return 416.666, and when I typed in:
=-" :0"
it did indeed return:
416.666

 

[Eric W]

Nothing in Autocorrect for my formula. Also, it's treated as a formula when I use the Evaluate Formula tool. Incidentally, if you take 6.94444 and multiply by 24*60 (thinking time again) you get 10000. Or if I format the cell as a time, it turns to 10:40:00 PM, with 6 days of duration.

 

[Joe4]

Nothing in Autocorrect for my formula. Also, it's treated as a formula when I use the Evaluate Formula tool. Incidentally, if you take 6.94444 and multiply by 24*60 (thinking time again) you get 10000. Or if I format the cell as a time, it turns to 10:40:00 PM, with 6 days of duration.

I was just showing one way in which that value could be returned (since most people were unable to reproduce it). Your first explanation is probably "more likely" related to what they are seeing (since there seems to be a correlation), but not the only way something like that can happen.

A few other things to check for:
1. Is there a Custom Format on that cell? If so, what is it?
2. Check for VBA code, specifically any Worksheet_Change code in the Sheet module, to see if any sort of "conversion" is going on. I have often seen (and helped) people write code to convert entries to other values, based on certain rules.