TEXT(MONTH(TODAY()),"mmm") returns Jan

[almagg]

this formula is returning 'Jan'
should be 'Jul', right?

?

 

[Jonmo1]

See if this helps

http://www.mrexcel.com/forum/showthread.php?t=566029

 

[T. Valko]

this formula is returning 'Jan'
should be 'Jul', right?

?

Remove the MONTH function from the formula:

=TEXT(NOW(),"mmm")

 

[taurean]

This will give you:
= TEXT(TODAY(),"mmm")

 

[Ruddles]

this formula is returning 'Jan'
should be 'Jul', right?
?

No, because MONTH(TODAY)) is 7, and the Excel date serial 7 - the 7th day after 1/1/1900 - is in January.

 

[almagg]

ok, understood.
what i need is to get the previous month out of this:

=MATCH("*"&TEXT(NOW(),"mmm") &"*",A4:HZ4,0)

i.e., so i am looking for the beginning of the Jun columns and the cell has:

06/01/11 - Jun-11

can't use numbers because e.g., 11 (Nov) will pick up this month:

01/01/11 - Jan-11

 

[almagg]

think i got it

=MATCH("*"&TEXT(NOW()-35,"mmm") &"*",A4:HZ4,0)

so wherever i am in the current month, the '-35' will put me in the previous month.
that's all i could think of.

 

[Jonmo1]

OK, so you're trying to get the previous month..

So instead of NOW, you want a date from LAST month?
=MATCH("*"&TEXT(NOW(),"mmm") &"*",A4:HZ4,0)


Try
=MATCH("*"&TEXT(TODAY()-DAY(TODAY()),"mmm") &"*",A4:HZ4,0)

TODAY()-DAY(TODAY()) = last day of last month.

 

[T. Valko]

think i got it

=MATCH("*"&TEXT(NOW()-35,"mmm") &"*",A4:HZ4,0)

so wherever i am in the current month, the '-35' will put me in the previous month.
that's all i could think of.

For the previous month try this...

=MATCH("*"&TEXT(NOW()-DAY(NOW()),"mmm")&"*",A4:HZ4,0)

 

[almagg]

that works.

maybe you can take a look here:
http://www.mrexcel.com/forum/showthread.php?t=566959

this is what i am working on.
and using the 'Flash' report:

=IF(ISNA(VLOOKUP($A2&"*",'[Flash Report FY11.xlsx]report'!$F$4:$HZ$500,151,FALSE)),"NA",IF(VLOOKUP($A2&"*",'[Flash Report FY11.xlsx]report'!$F$ 4:$HZ$500,151,FALSE)=0,L2,VLOOKUP($A2&"*",'[Flash Report FY11.xlsx]report'!$F$4:$HZ$500,151,FALSE)))

of course the value '151' needs to be replaced with something but that is 13 columns over from the start of the June data. and the final VLOOKUP would subtract June from May which is 4 columns to the left of June.

much obliged.