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

=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)

"Victor Delta" wrote in message
...
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" wrote in message
...
=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

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.

--
Thanks,
Shane Devenshire


"Victor Delta" wrote:

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

"ShaneDevenshire" wrote in
message ...
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