| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Earliest date using WEEKNUMBER
Hi,
Dates in column A and WEEKNUMBER in cloumn B (referring to A). How do I display (in C) only the earliest date (without duplicates) for each week (Ie the Monday's date) Thanks, -- Traa Dy Liooar Jock |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Earliest date using WEEKNUMBER
Jock,
With your list of dates in column A and the corresponding wee numbers in column B put this in C1 =DATE(YEAR(A1),1,B1*7-(DAY(DATE(YEAR(A1),1,1)+7-WEEKDAY(DATE(YEAR(A1),1,1),1)))) Then put this in C2 and drag down =IF(B2=B1,"",DATE(YEAR(A2),1,B2*7-(DAY(DATE(YEAR(A2),1,1)+7-WEEKDAY(DATE(YEAR(A2),1,1),1))))) Mike "Jock" wrote: Hi, Dates in column A and WEEKNUMBER in cloumn B (referring to A). How do I display (in C) only the earliest date (without duplicates) for each week (Ie the Monday's date) Thanks, -- Traa Dy Liooar Jock |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Earliest date using WEEKNUMBER
Great stuff. Thanks Mike
-- Traa Dy Liooar Jock "Mike H" wrote: Jock, With your list of dates in column A and the corresponding wee numbers in column B put this in C1 =DATE(YEAR(A1),1,B1*7-(DAY(DATE(YEAR(A1),1,1)+7-WEEKDAY(DATE(YEAR(A1),1,1),1)))) Then put this in C2 and drag down =IF(B2=B1,"",DATE(YEAR(A2),1,B2*7-(DAY(DATE(YEAR(A2),1,1)+7-WEEKDAY(DATE(YEAR(A2),1,1),1))))) Mike "Jock" wrote: Hi, Dates in column A and WEEKNUMBER in cloumn B (referring to A). How do I display (in C) only the earliest date (without duplicates) for each week (Ie the Monday's date) Thanks, -- Traa Dy Liooar Jock |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Earliest date using WEEKNUMBER
=IF(B2<B1,A2-WEEKDAY(A2,3),"")
-- David Biddulph "Jock" wrote in message ... Hi, Dates in column A and WEEKNUMBER in cloumn B (referring to A). How do I display (in C) only the earliest date (without duplicates) for each week (Ie the Monday's date) Thanks, -- Traa Dy Liooar Jock |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I find the earliest date? | Excel Discussion (Misc queries) | |||
| convert date (YYYYMMDD) to weeknumber | Excel Worksheet Functions | |||
| Calculate a date based on a weeknumber | Excel Discussion (Misc queries) | |||
| Calculate a date based on a weeknumber | Excel Worksheet Functions | |||
| Earliest Date to populate? | Excel Worksheet Functions | |||
Linear Mode
