=MONTH(1&"AUG") will return 8!? How?

[PP3321]

Thank you again in advance to all...
You help me learn so much about Excel...

I want to understand the logic behind this.
Can you please give me a clue...?

=MONTH(1&"AUG")

How is it possible that it returns 8...?

 

[Khalid NGO]

Hi,
1&"Aug" actually converts the text date into number date, and MONTH function produces the month number of Aug i.e. 8.

If you enter =1&"Aug" in any cell it will give you the real date of 1-Aug-2016 (current year).

You can use any number instead of 1.

Regards,

 

[PP3321]

@Khalid NGO

Thank you so much for your answer.

If I just do
=1&"Aug"

It will return string:
1Aug

Maybe I am missing something...?

 

[Khalid NGO]

Oops my bad.

Yes it will return string, you need to do a mathematical operation, like:
=--(1&"aug")
or
=(1&"aug")+0

Another variant is:
=TEXT(1&"aug","m")
string

=--TEXT(1&"aug","m")
number

 

[PP3321]

@Khalid NGO

Thank you...

I did the following, which returns 8

=MONTH(DATEVALUE(1&"AUG"))

So Month function is so smart that it converts to date serial without DATEVALUE function...?

 

[bosco_yip]

I did the following, which returns 8
=MONTH(DATEVALUE(1&"AUG"))
..........

This is enough :

=MONTH(1&"AUG")

Regards
Bosco

 

[NARAYANK991]

Hi ,

=MONTH(Number & "Aug")

works for Number between 1 and 31.

=MONTH("Aug" & Number)

works for Number between 0 and 99.

Both the above constructs return the value 8 as long as Number is within the above limits.

Generally speaking , the above construct is used when the month name is available in a cell , so that to get the month from the name is easy when all that has to be done is either :

=MONTH(1 & A1)

or

=MONTH(A1 & 1)

where A1 can have either Jan or January , Feb or February and so on.

Narayan

 

[PP3321]

Thank you all...