Return week ending date from Week Number

[cazzzac]

Hi there,

I have a dataset that is entered daily into the date column (Column B) of a worksheet. Each daily entry has the corresponding week number placed next to it through the WeekNum function (Column A), which has weeks of the year marked as numbers (27, 28, 29, 30 etc.). I am using the week number column to summarise that week's values on another worksheet.

I would like to create a formula on the summary worksheet that returns the week ending date by looking up the friday or saturday value (column B) next to the week number (column A). For example, week number 27 on the summary worksheet, will look for 27 in the WeekNum column on the dataset worksheet, find the text Friday or Saturday, then return the full date value (Friday, 4th July 2014) to the week ending column on the summary worksheet.

Any ideas as to how this could be constructed?

I was thinking the logic would be:
For Week number (27), search for 27 on the dataset sheet in column A; where 27 is found, search for text string 'Friday' in column B, then return the full value for that cell ("Friday, 4th July, 2014")

 

[Teeroy]

Where is the date that your Weeknum uses? It would be easiest to start with that.

 

[cazzzac]

The date is on the Dataset sheet, and i want the week ending to go on the Data Summary sheet.
Alternatively, if i could match the week ending against each date entry, i could make that flow through to the Data Summary sheet.

 

[cazzzac]

Alternatively, is there a way to create a formula that returns the sixth day of the week based on a particular date? e.g. use the WeekDay function to read a particular date and return the sixth day of the week (the Friday) in relation to that date?

 

[MARK858]

Try

=A1+7-WEEKDAY(A1+2,2)

Where A1 is your cell containing the date

 

[kvsrinivasamurthy]

The below formula gives the Firday date of the WEEKNUMBER in B4 cell. Format the cell Custom --> dddd dd/mm/yyyy to show Day & Date.

=DATE(2014,1,1)+($B$4-1)*7+6-WEEKDAY(DATE(2014,1,1),1)

 

[Teeroy]

Mark858 went exactly where I was heading.

 

[cazzzac]

Nice one, the only minor issue with Mark's solution is that the Excel WeekNum formula counts Saturday as the final day of the week, while this formula counts Friday. So you end up with a slight mismatch (see image).

I blindly played around a bit with the formula and got it to work for Saturday as the end day.

=A1+7-WEEKDAY(A1+2,2)
changed to
=A1+7-WEEKDAY(A1+2,3)

Kvsrinivasamurthy's solution also worked against the week number, but i found I couldn't drag the formula for some reason.

 

[MARK858]

the only minor issue.... Excel WeekNum formula counts Saturday as the final day of the week, while this formula counts Friday

that's because you asked for the Friday

read a particular date and return the sixth day of the week (the Friday) in relation to that date

To get the Saturday you can shorten the formula to

=A1+7-WEEKDAY(A1)

Excel Workbook
	A	B	C
1	Tue 01/07/2014	27	Sat 05/07/2014
2	Wed 02/07/2014	27	Sat 05/07/2014
3	Thu 03/07/2014	27	Sat 05/07/2014
4	Fri 04/07/2014	27	Sat 05/07/2014
5	Sat 05/07/2014	27	Sat 05/07/2014
6	Sun 06/07/2014	28	Sat 12/07/2014
7	Mon 07/07/2014	28	Sat 12/07/2014
8	Tue 08/07/2014	28	Sat 12/07/2014
9	Wed 09/07/2014	28	Sat 12/07/2014
10	Thu 10/07/2014	28	Sat 12/07/2014
11	Fri 11/07/2014	28	Sat 12/07/2014
12	Sat 12/07/2014	28	Sat 12/07/2014
13	Sun 13/07/2014	29	Sat 19/07/2014
14	Mon 14/07/2014	29	Sat 19/07/2014
15	Tue 15/07/2014	29	Sat 19/07/2014
16	Wed 16/07/2014	29	Sat 19/07/2014
17	Thu 17/07/2014	29	Sat 19/07/2014
18	Fri 18/07/2014	29	Sat 19/07/2014
19	Sat 19/07/2014	29	Sat 19/07/2014
Sheet5

 

[cazzzac]

Thanks for that, and sorry for the confusion. It works properly now