Convert WEEKNUM into last day Date

[Jmbostock]

Hi there

I've a small problem. I've a data list with revenue etc. in it fo
various dates throughout this year. I want to put it into a pivot tabl
where the data is grouped by weekly total revenue.

The way i'm doing it is having a WEEKNUM formula calculate what week i
is, then create a pivot table from that. Only now i've got week 1,2,
etc. on the pivot table, but no real date to tie them too. I kno
they're going to ask me what date does week 1,2,3 end on, so i wa
wondering if there was a way to find the date of the sunday in th
week.

So for every day in a particular week (starting on a monday), thi
formula would now show the date of the sunday in the week.

Thanks

Jame

 

[Norman Harker]

Hi James!

You have your source date for the WEEKNUM formula. Use this source
date in the following formula:

=IF(WEEKDAY(A1)<>1,DATE(YEAR(A1),MONTH(A1),DAY(A1)+7-WEEKDAY(A1)+1),A1
)

I'm assuming that if the day is actually Sunday, you want that day.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Jmbostock]

Exactly what i needed.

Thanks so much

Jame

 

[Daniel.M]

Hi James,

You're searching for current/next Sunday:

=A1-WEEKDAY(A1-1)+7

Regards,

Daniel M.

 

[Norman Harker]

Hi Daniel!

Much neater!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.