Ad-Free CAclubindia and Daily Updates on your mobile. Subscribe Now!

Interesting excel formula

CA CS CIMA Prakash Somani (Landmark Group) (23502 Points)

08 May 2009

Let say A1 type ="2-1"+"2-1"
Do you think there of result?
Yes, it equals to 76036
Why?

Also:
="2-1"-"2-1" -----> 0
="2-1"*"2-1" -----> 1445368324
="2-1"/"2-1" ------> 1

Thanks

Reply
Follow
Share

3 Replies

PRAVEEN KUMAR (MBA (Finance) B.Com.(P)) (3443 Points)
Replied 23 May 2009

because Excel is interpreting “2-1” as the date value for 2/1/2009 which has the serial number 39845. When added to itself, yields the result you saw, 79690, which if formatted as a date is 3/7/2018.

by d way it is a Good catch! I had not seen the more obvious solution of converting date-like numbers to serialdates....

That would suggest that
="2-1"*"2-1" -----> 1445368324
is actually:
="2-1-4"*"2-1-4"-----> 1445368324

since
="2-1"*"2-1" -----> 1587624025

Hope the above clarifies the formula

Thanks

Praveen

Upgrade to CAclubindia Pro. Subscribe Now!

(Guest)

yes praveen is right. any digit written in date format (mention of year is not necessary) will be calculated as date and as per excel dates are calculated in numerical which was started from year 1900. So all dates are given in cell are calculated as numerical. Thats why we see this type of answers.

>> Rajesh