explain this one to me....

D

Dave F

....X46 contains 7/31/2007. Formatted as a date.

Y45 contains =TEXT(MONTH(X46),"mmm")

Result is "Jan"

Huh?
 
E

excelent

but ur right - it is strange, cus =month(X46) returns 07
so why not Jul with =TEXT(MONTH(X46),"mmm") ?????????

"Dave F" skrev:
 
B

Biff

For those that might not have figured it out:

MONTH is returning 7 which is correct.

However, the 7 also evaluates to serial date 7 when used in this formula:
TEXT( 7,"mmm").

Serial date 7 is Jan 7 1900 so the result of the formula is Jan which is
correct. So, the formula should be:

=TEXT(X46,"mmm")

Biff
 
M

Mike Rogers

Biff

Why is it that anything I put in gives me "Jan"??? ie 12/31/1900, 11/16/06,
or 9/17/07???

Mike Rogers
 
D

Dave F

Because all those serial numbers resolve to days in January, 1900 as Biff
explained.

Dave
 
B

Biff

Are you doing it like this:

=TEXT(12/31/1900,"mmm")
=TEXT(11/16/06,"mmm")
=TEXT(9/17/07,"mmm")

If so, what you're getting is:

12 divided by 31 divided by 1900 = 0.000203735144312394
11 divided by 16 divided by 6 = 0.114583333333333
9 divided by 17 divided by 7 = 0.0756302521008403

In the formula evaluation the decimal portion is truncated and you're left
with 0.

So the formula is evaluating as serial date 0 or 1/0/1900 = Jan.

That's one of Excel's anomalies, you can calculate a date to be the 0th day
of a month. This can be taken advantage of in that the 0th day of the month
actually refers to the last day of the previous month. Starting to get off
topic and it can be complicated trying to figure out in what situations this
isn't always the case. For example, if the 0th day of the month refers to
the last day of the previous month then why doesn't 1/0/1900 return Dec? Yet
WEEKDAY(0,2) retruns 6 (Saturday) which is referring to Saturday, Dec 31
1899. Did I open a gigantic can of worms? <BG>

So, try your formula like this:

=TEXT("12/31/1900","mmm")

Biff
 
M

Mike Rogers

Biff

Thanks for the lessons, (math, excel, calanders, and what ever else I did't
acknowledge) and in such a short concise lesson :)

Mike Rogers

PS Don't worry about that "can of worms" they have been opened and closed
so many times, the hinges are gone!!!!
 
Top