Convert WEEKNUM into last day Date

  • Thread starter Thread starter Jmbostock
  • Start date Start date
J

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
 
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.
 
Hi James,

You're searching for current/next Sunday:

=A1-WEEKDAY(A1-1)+7

Regards,

Daniel M.
 
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.
 
Back
Top