Earliest date using WEEKNUMBER

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jock - 14 Sep 2007 12:18 GMT

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,

Signature

Traa Dy Liooar

Jock

Reply to this Message

Mike H - 14 Sep 2007 14:02 GMT

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

> 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,

Reply to this Message

Jock - 14 Sep 2007 14:12 GMT

Great stuff. Thanks Mike

Signature

Traa Dy Liooar

Jock

> Jock,
>
[quoted text clipped - 14 lines]
> >
> > Thanks,

Reply to this Message

David Biddulph - 17 Sep 2007 10:17 GMT

=IF(B2<>B1,A2-WEEKDAY(A2,3),"")

Signature

David Biddulph

> Hi,
> Dates in column A and WEEKNUMBER in cloumn B (referring to A).
[quoted text clipped - 3 lines]
>
> Thanks,

Reply to this Message