Weekending Date w/Less Than 3 days Assoc. with Prior Month
 

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

Weekending Date w/Less Than 3 days Assoc. with Prior Month
 

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

Weekending Date w/Less Than 3 days Assoc. with Prior Month
 

Hello,

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

Regards,
Bernd

Weekending Date w/Less Than 3 days Assoc. with Prior Month
 

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

Weekending Date w/Less Than 3 days Assoc. with Prior Month
 

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

Weekending Date w/Less Than 3 days Assoc. with Prior Month
 

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

Weekending Date w/Less Than 3 days Assoc. with Prior Month
 

Hello,

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

Regards,
Bernd

Weekending Date w/Less Than 3 days Assoc. with Prior Month
 

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