Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display "this week" column headers w/date & day of week? | Excel Worksheet Functions | |||
from date return week date range | Excel Worksheet Functions | |||
Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
Finding the Monday date based on a different date in same week | Excel Worksheet Functions | |||
I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) |