I'm using Excel 2003. I have a table of week ending dates for each week of
the year. Assume the week ends on Friday of each week. I'd like a formula
to associate each week ending date with a month of the year. But if the last
week of the month has 4 days or more, count it in the current or ending
month. If the last week has 3 or less days associate it with the upcoming
month.

For instance, week ending Friday 3-Jul-2009, has four days in June and
should be classified as a June week. Week ending 4-Sep-2009 should be
classified in September. I've researched cpearson.com and many of the
suggested functions with no luck so far.

Many Thanks

Not real clear what you want.

This will return the month name based on your criteria.

A1 = some date

=TEXT(IF(DAY(A1)<=3,A1-DAY(A1),A1),"mmmm")

--
Biff
Microsoft Excel MVP


"MoneyMan" wrote in message
...
I'm using Excel 2003. I have a table of week ending dates for each week
of
the year. Assume the week ends on Friday of each week. I'd like a
formula
to associate each week ending date with a month of the year. But if the
last
week of the month has 4 days or more, count it in the current or ending
month. If the last week has 3 or less days associate it with the upcoming
month.

For instance, week ending Friday 3-Jul-2009, has four days in June and
should be classified as a June week. Week ending 4-Sep-2009 should be
classified in September. I've researched cpearson.com and many of the
suggested functions with no luck so far.

Many Thanks

Please excuse the lack of clarity in my question. However, you hit the nail
exactly on the head. The formula does exactly what is required.

"T. Valko" wrote:

Not real clear what you want.

This will return the month name based on your criteria.

A1 = some date

=TEXT(IF(DAY(A1)<=3,A1-DAY(A1),A1),"mmmm")

--
Biff
Microsoft Excel MVP


"MoneyMan" wrote in message
...
I'm using Excel 2003. I have a table of week ending dates for each week
of
the year. Assume the week ends on Friday of each week. I'd like a
formula
to associate each week ending date with a month of the year. But if the
last
week of the month has 4 days or more, count it in the current or ending
month. If the last week has 3 or less days associate it with the upcoming
month.

For instance, week ending Friday 3-Jul-2009, has four days in June and
should be classified as a June week. Week ending 4-Sep-2009 should be
classified in September. I've researched cpearson.com and many of the
suggested functions with no luck so far.

Many Thanks

Good guess on my part! Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"MoneyMan" wrote in message
...
Please excuse the lack of clarity in my question. However, you hit the
nail
exactly on the head. The formula does exactly what is required.

"T. Valko" wrote:

Not real clear what you want.

This will return the month name based on your criteria.

A1 = some date

=TEXT(IF(DAY(A1)<=3,A1-DAY(A1),A1),"mmmm")

--
Biff
Microsoft Excel MVP


"MoneyMan" wrote in message
...
I'm using Excel 2003. I have a table of week ending dates for each
week
of
the year. Assume the week ends on Friday of each week. I'd like a
formula
to associate each week ending date with a month of the year. But if
the
last
week of the month has 4 days or more, count it in the current or ending
month. If the last week has 3 or less days associate it with the
upcoming
month.

For instance, week ending Friday 3-Jul-2009, has four days in June and
should be classified as a June week. Week ending 4-Sep-2009 should be
classified in September. I've researched cpearson.com and many of the
suggested functions with no luck so far.

Many Thanks

Hello,

Good guess, Biff, but why not just
=TEXT(A1-3,"mmmm")
then :-)

Regards,
Bernd

why not just
=TEXT(A1-3,"mmmm")

Yeah, that'll work.

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
Hello,

Good guess, Biff, but why not just
=TEXT(A1-3,"mmmm")
then :-)

Regards,
Bernd

Hello,

=MONTH(A1)-(DAY(A1)<4)
will give you the month as a number.

Regards,
Bernd

=MONTH(A1-3)
seems to be better.