CODEWITHSUNDEEP
excelexcel-formula
Rick Hitchcock
Rick Hitchcock

Reputation: 35670

Strange result from multiplying string by number

In Excel 2010, "3-5" * 1 equals 41703:

enter image description here

A number is returned for any string in this format, as long as the first number is between 1 and 12, and the second number is between 1 and 99.

Below is a "multiplication" table, which hints at a pattern, but it throws a real curve ball at times (note the highlighted cell):

enter image description here

Questions:

  1. Can you confirm that other versions of Excel give the same results?
  2. Is there a pattern here?

If the answers to both questions are, "Yes," then this may be something we can exploit, especially when working with array formulas.

Upvotes: 1

Views: 111

Answers (1)

pnuts
pnuts

Reputation: 59450

  1. In equivalent circumstances (eg locale) other versions of Excel will give the same results.
  2. Yes, the pattern is the date. 3-5 is being interpreted, for you, as March 5 (of this year) (Date serial number in 1900 system of 41703). The highlighted cell is because February does not have 30 days.

Upvotes: 1

Related Questions