# nearest friday to date and if date is a friday return that friday not the following

1. ## nearest friday to date and if date is a friday return that friday not the following

Hi there,

I have found several similar formula's on here but not quite what I need.

I need a date in column a to have 60 days added and then to find the nearest Friday to that date. If the date 60 days on is a friday I want to return that friday and not the next.

i.e.
Date in column A is 25/01/2010 date 60 days is 26/03/2010 which is a frdiay so formula should return this date. It should return this date for anything between 19/01/2010 and 25/01/2010.
The formula I was using is:
``Please Login or Register  to view this content.``
However, this returns the following friday if the date + 60 days is a friday.

Thanks

2. ## Re: nearest friday to date and if date is a friday return that friday not the followi

Assuming by nearest your mean next - and taking into account that if result is a Friday that date should persist then perhaps:

B1: =A1+60+CHOOSE(WEEKDAY(A1+60,2),4,3,2,1,0,7,6)

3. ## Re: nearest friday to date and if date is a friday return that friday not the followi

Or perhaps =A1+60+MOD(9-WEEKDAY(A1),7)

4. ## Re: nearest friday to date and if date is a friday return that friday not the followi

Hi there, that formula did not work.

I mean if the date = 60 days is a sat-fri, say 27/02/2010 to 05/03/2010 it should return 05/03/2010 for all of these.

Does that make sense?

The formula I had returns the friday 5th march when A1 + 60 = to anywhere 27/02/2010 to 04/2010 but for the Friday it returns 12/03/2010

Thanks
Libby

5. ## Re: nearest friday to date and if date is a friday return that friday not the followi

Your last post makes no sense to me I'm afraid -- hopefully someone else can follow it.

Why would 27/2 + 60 -> 5/3 ?

The result should be 30-Apr - given 27/2 + 60 -> 28/4 which is a Wed - hence +2 is 30-Apr.

6. ## Re: nearest friday to date and if date is a friday return that friday not the followi

No I meant 27/02 would be the date returned after 60 days already added. so original date would be 29/12/2009. Sorry for confusion.

So 29/12/2009 + 60 days is 27/02/2010 which is a Sat, this then needs to return the following friday which will be 05/03/2010. All dates therefore from 29/12/2009 up to and including 04/01/2010 should return 05/03/2010 as the friday I want.

Thank you

7. ## Re: nearest friday to date and if date is a friday return that friday not the followi

Originally Posted by DonkeyOte
=A1+60+CHOOSE(WEEKDAY(A1+60,2),4,3,2,1,0,7,6)
I imagine that should be

=A1+60+CHOOSE(WEEKDAY(A1+60,2),4,3,2,1,0,6,5)

or Darkyam's suggestion would work for you

This is mine:

=A1+60+7-WEEKDAY(A1+60+1)

8. ## Re: nearest friday to date and if date is a friday return that friday not the followi

Thats it. I think it was me not thinking it was correct.

Thanks to everyone.

9. ## Re: nearest friday to date and if date is a friday return that friday not the followi

I imagine that should be

=A1+60+CHOOSE(WEEKDAY(A1+60,2),4,3,2,1,0,6,5)
Yes, thanks dll... typo on my part - best always to test

10. ## Re: nearest friday to date and if date is a friday return that friday not the following

Try this formula. It should return the nearest (not next) Friday to 60 days from the date in A1.
=(A1+60+4)-WEEKDAY(A1+60+4-6)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1