Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to format a cell from date to week?
Dear all,
How to format a cell date like "2009-12-10" to year /week format "YYYYWW" = 200950? Also how to determine number of weeks between 2 weeks like from 200950 to 201002 = 5 weeks? BR//nginhong |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to format a cell from date to week?
You cannot achieve this with cell formatting. Excel does not support weeks
in a date format. To change a date to yyyyww, use: =year(a1)*100+weeknum(a1) The number of weeks difference between two of these cells, use: =(int(a2/100)-int(a1/100))*52+(mod(a2,100)-mod(a1,100)) This calculates to 4 weeks, not 5, just as the difference between 201001 and 201002 is 1, not 2. If you really want 5 weeks as the answer, add 1 to the above. Regards Fred "nginhong" wrote in message ... Dear all, How to format a cell date like "2009-12-10" to year /week format "YYYYWW" = 200950? Also how to determine number of weeks between 2 weeks like from 200950 to 201002 = 5 weeks? BR//nginhong |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to format a cell from date to week?
Ans 1:
=YEAR(A1)&WEEKNUM(A1) Ans 2: Assume A3 holds: 200950, and A4 holds 201002 =(LEFT(A4,4)-LEFT(A3,4))*53+RIGHT(A4,2)-RIGHT(A3,2) "nginhong" wrote: Dear all, How to format a cell date like "2009-12-10" to year /week format "YYYYWW" = 200950? Also how to determine number of weeks between 2 weeks like from 200950 to 201002 = 5 weeks? BR//nginhong |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date format and day of week | Setting up and Configuration of Excel | |||
Date format with day of week and date | Excel Discussion (Misc queries) | |||
How can I convert a date format to an ISO week format (in EXCEL)? | Excel Discussion (Misc queries) | |||
Format an excel column as a date for a 5 day week for a year. | Excel Discussion (Misc queries) | |||
Custom Date format ie. 01.01.05 W1 (W1 is week 1) | Excel Discussion (Misc queries) |