Only want Friday dates

[west7711]

Hi, I have a spreadsheet that have a series of dates for the whole year of 2012 and some in 2013. I need a formula to give me a current Friday date base on the date shown in the spreadsheet. Based on a seven day week Sat thru Fri. See example below, column 2 is the Friday date.

11/1/12 --> 11/2/12
11/12/12 --> 11/16/12
11/14/12 --> 11/16/12
5/21/12 --> 5/25/12
5/28/12 --> 6/1/12
7/1/12 --> 7/6/12

Thanks,

[daddylonglegs]

If A1 contains a date you can use one of these formulas to get the following Friday

=A1+8-WEEKDAY(A1+2)

or

=A1+7-WEEKDAY(A1+1)

These differ only when A1 is a friday, the former will return the following Friday, the latter the same date as A1

[judgeh59]

=7-WEEKDAY(A1,16)+A1

I like Daddylonglegs answer....this is what I came up with....the WEEKDAY function has an option to allow for different start days for the beginning of the week....HTH....

[xladept]

Hi West7711,

Here's an UDF:

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code under it

Then in the cell where you want the date type "=GetFriday(H23)" or wherever the cue date is

[popipipo]

Or this one:

Please Login or Register  to view this content.

[west7711]

Thanks!

[Kevin UK]

Hi West7711

Or another way, assuming your data is in A1:A6, in B1 & copy down.

Please Login or Register  to view this content.

[judgeh59]

Hi Kevin - just a thought as you are new - it is usually suggested that you mark the thread solved if in fact it is....there is a thread tool dropdown at the top of the thread on the right side....have a great day....