Day/Date/Week
 

Good Afternoon All,

Thanks to some help received here on my thread - Name The Date2 - I now need
to be able to name the Week in the month (ie 1-5) that the Day (Mon/Tues...)
falls in.
Example Spreadsheet below

A B C
1 = Today()
2 Week No Day Date -
Header Row
3 Tue 01
4 Wed 02
5 Thu 03
6 Fri 04

The Formula In Column B3 & copied down is
=TEXT(DATE(YEAR($A$1),MONTH($A$1),TRIM(C3)),"DDD")
which gives the Day of the date in Column C
Now in Column A, I would like a way to name the week of the month, ie 1 to
5. In this case the first Monday (the 7th) would be in Week2, but the
formulae I have tried, (including combinations of IF statements don't seem
to work, as only can nest 7), want to use the dates 01-07 as Week1, which is
obviously incorrect in this case.
I am trying to summarize all my company's direct debits, but have found it
difficult as the DAYS of the month fall on Different DATES of the month.
I hope I have explained this okay, and the formatting works when I post it!

As usual, any help is most appreciated.
Cheers
Mathew

Day/Date/Week
 

=INT(C3/7)+1

--
__________________________________
HTH

Bob

"Mathew" wrote in message
...
Good Afternoon All,

Thanks to some help received here on my thread - Name The Date2 - I now
need to be able to name the Week in the month (ie 1-5) that the Day
(Mon/Tues...) falls in.
Example Spreadsheet below

A B C
1 = Today()
2 Week No Day
Date - Header Row
3 Tue 01
4 Wed 02
5 Thu 03
6 Fri 04

The Formula In Column B3 & copied down is
=TEXT(DATE(YEAR($A$1),MONTH($A$1),TRIM(C3)),"DDD")
which gives the Day of the date in Column C
Now in Column A, I would like a way to name the week of the month, ie 1 to
5. In this case the first Monday (the 7th) would be in Week2, but the
formulae I have tried, (including combinations of IF statements don't seem
to work, as only can nest 7), want to use the dates 01-07 as Week1, which
is obviously incorrect in this case.
I am trying to summarize all my company's direct debits, but have found it
difficult as the DAYS of the month fall on Different DATES of the month.
I hope I have explained this okay, and the formatting works when I post
it!

As usual, any help is most appreciated.
Cheers
Mathew

Day/Date/Week
 

Thanks Bob, worked a treat, & so simple!
Mathew
"Bob Phillips" wrote in message
...
=INT(C3/7)+1

--
__________________________________
HTH

Bob

"Mathew" wrote in message
...
Good Afternoon All,

Thanks to some help received here on my thread - Name The Date2 - I now
need to be able to name the Week in the month (ie 1-5) that the Day
(Mon/Tues...) falls in.
Example Spreadsheet below

A B C
1 = Today()
2 Week No Day Date - Header Row
3 Tue 01
4 Wed 02
5 Thu 03
6 Fri 04

The Formula In Column B3 & copied down is
=TEXT(DATE(YEAR($A$1),MONTH($A$1),TRIM(C3)),"DDD")
which gives the Day of the date in Column C
Now in Column A, I would like a way to name the week of the month, ie 1
to 5. In this case the first Monday (the 7th) would be in Week2, but the
formulae I have tried, (including combinations of IF statements don't
seem to work, as only can nest 7), want to use the dates 01-07 as Week1,
which is obviously incorrect in this case.
I am trying to summarize all my company's direct debits, but have found
it difficult as the DAYS of the month fall on Different DATES of the
month.
I hope I have explained this okay, and the formatting works when I post
it!

As usual, any help is most appreciated.
Cheers
Mathew

Day/Date/Week
 

Hi Bob, Sorry I spoke too soon - the =INT(C3/7)+1 does not quite work for
all cases.
For August 01 2008, the Date 01 is a Friday & shows as week 1. But the
following Monday, Also shows as week1, which is not right in this case.
I am trying to think of a work around, but if you have any more thoughts...

Cheers Bob,
Mathew
"Mathew" wrote in message
...
Good Afternoon All,

Thanks to some help received here on my thread - Name The Date2 - I now
need to be able to name the Week in the month (ie 1-5) that the Day
(Mon/Tues...) falls in.
Example Spreadsheet below

A B C
1 = Today()
2 Week No Day
Date - Header Row
3 Tue 01
4 Wed 02
5 Thu 03
6 Fri 04

The Formula In Column B3 & copied down is
=TEXT(DATE(YEAR($A$1),MONTH($A$1),TRIM(C3)),"DDD")
which gives the Day of the date in Column C
Now in Column A, I would like a way to name the week of the month, ie 1 to
5. In this case the first Monday (the 7th) would be in Week2, but the
formulae I have tried, (including combinations of IF statements don't seem
to work, as only can nest 7), want to use the dates 01-07 as Week1, which
is obviously incorrect in this case.
I am trying to summarize all my company's direct debits, but have found it
difficult as the DAYS of the month fall on Different DATES of the month.
I hope I have explained this okay, and the formatting works when I post
it!

As usual, any help is most appreciated.
Cheers
Mathew