Help with an Excel formula?

[Victor Delta]

I have an Excel spreadsheet with column headings (in Row 1) - some of which
are dates (past, present and future, one for each month) and others are
text.

Can anyone please help me with a formula that will give the column number
for the column with the heading that contains a date with the same month as
today(). It would be simpler if there weren't the text headings as well to
contend with!

Many thanks,

V

 

[Bob Phillips]

=MATCH(1,(MONTH(TODAY())=MONTH(1:1))*(YEAR(TODAY())=YEAR(1:1)),0)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ShaneDevenshire]

Hi,

It really depends on how your months are entered in the headings, for
example if they are Jan, Feb,... then the following formula will return 9 for
september, the current month:

=MATCH(TEXT(NOW(),"mmm"),A1:L1)

where A1:L1 are your month titles.

 

[Victor Delta]

=MATCH(1,(MONTH(TODAY())=MONTH(1:1))*(YEAR(TODAY())=YEAR(1:1)),0)

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do not try to do this manually.
When editing the formula, it must again be array-entered.

Bob

Many thanks - works a treat!

Regards,

V

 

[Victor Delta]

Hi,

It really depends on how your months are entered in the headings, for
example if they are Jan, Feb,... then the following formula will return 9
for
september, the current month:

=MATCH(TEXT(NOW(),"mmm"),A1:L1)

where A1:L1 are your month titles.

Shane

Thanks for suggesting this but, even changing the bit in inverted commas to
"mmm yy" to correspond to the month formatting I'm using, still gives an
erroneous output.

Regards,

V