| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week number converted to date
I have used
"=weeknum(A2;2)" to covert actual dates into the week of the year into which they fell.ie 009/02/2006 is in week 7. I now want to be able to have a column heading that reads "Week comencing 02/022006", so I need to convert week7 back to the first day(monday) of that week. Is this possible, if so how. Bob |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Week number converted to date
=TODAY()-WEEKDAY(TODAY(),3)-7*WEEKNUM(TODAY()-WEEKDAY(TODAY(),3))+A1*7
where A1 is that week number -- HTH Bob Phillips (remove nothere from email address if mailing direct) "RJG" wrote in message ups.com... I have used "=weeknum(A2;2)" to covert actual dates into the week of the year into which they fell.ie 009/02/2006 is in week 7. I now want to be able to have a column heading that reads "Week comencing 02/022006", so I need to convert week7 back to the first day(monday) of that week. Is this possible, if so how. Bob |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Week number converted to date
Maybe.........
=38718+1+(7*weeknumber) Vaya con Dios, Chuck, CABGx3 "RJG" wrote: I have used "=weeknum(A2;2)" to covert actual dates into the week of the year into which they fell.ie 009/02/2006 is in week 7. I now want to be able to have a column heading that reads "Week comencing 02/022006", so I need to convert week7 back to the first day(monday) of that week. Is this possible, if so how. Bob |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Week number converted to date
=A2-weekday(a2,2)+1
or ="Week commencing " & Text(A2-weekday(a2,2)+1,"dd/mm/yyyy") -- Regards, Tom Ogilvy "RJG" wrote in message ups.com... I have used "=weeknum(A2;2)" to covert actual dates into the week of the year into which they fell.ie 009/02/2006 is in week 7. I now want to be able to have a column heading that reads "Week comencing 02/022006", so I need to convert week7 back to the first day(monday) of that week. Is this possible, if so how. Bob |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Week number converted to date
Hi Bob, If you are looking for the Monday of any given week, =A2-WEEKDAY(A2)+2 should do the job, provided your date is located in A2. HTH Cheers Carim |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Week number converted to date
Sorry to be stupid but I am not a regular excel user,
As you correctly state the above gives a number (38733) in the required cell, if I click on format cell then I can get the number to show corrctly as 16/01/2006. However as soon as I include text with it the formatting dissapears and reverts back to the number. Is there anyway I can format the following straight into the required "Week com 16/01/2006". ="week com "&TODAY()-WEEKDAY(TODAY();3)-7*WEEKNUM(TODAY()-WEEKDAY(TODAY();3))+L1*7 With thanks Bob |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Week number converted to date
Bob,
Try ="week com "&TEXT(TODAY()-WEEKDAY(TODAY();3)-7*WEEKNUM(TODAY()-WEEKDAY(TODAY();3))+L1*7 ,"mm/dd/yyyy") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "RJG" wrote in message oups.com... Sorry to be stupid but I am not a regular excel user, As you correctly state the above gives a number (38733) in the required cell, if I click on format cell then I can get the number to show corrctly as 16/01/2006. However as soon as I include text with it the formatting dissapears and reverts back to the number. Is there anyway I can format the following straight into the required "Week com 16/01/2006". ="week com "&TODAY()-WEEKDAY(TODAY();3)-7*WEEKNUM(TODAY()-WEEKDAY(TODAY();3))+L1*7 With thanks Bob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to display date as week number | Excel Discussion (Misc queries) | |||
| Date converted to Text Number | Excel Discussion (Misc queries) | |||
| How can I calculate the week number from a date, eg 21/06/05 = 26 | Excel Worksheet Functions | |||
| Accidentally converted date mm/dd/yy to a 5 digit number | Excel Worksheet Functions | |||
| I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) | |||
Linear Mode
