Thread: Populate date based on a date in another cell

Hello,

Newby here.

I hope someone can help me with the following scenario:

I have a date in cell "A1", a formula in cell "B1" that populates the day of the week based on the date in cell "A1". What I need is a formula for cell "C1" that will populate the date of the upcoming FRIDAY based on the date in cell "A1".

What ever the date in cell "A1", the date in cell "C1" needs to always display the date of the next/ upcoming Friday. Is this possible?

Anthony

Hi ascottbag and welcome to the forum

YES it is possible. Try this.

C1:  =A1-WEEKDAY(A1)+IF(WEEKDAY(A1)<6,6,13)

Make sure your date cells are formatted as dates. Test it to make sure.

What should C1 show if A1 is a Friday? Assuming that C1 should show the same date as A1 in that situation try

=A1-WEEKDAY(A1+1)+7

or if you want to see the following Friday if A1 is Friday change to

=A1-WEEKDAY(A1+2)+8

My formula was the next Friday if the date in A1 was a Friday as per

needs to always display the date of the next/ upcoming Friday

Here is another formula that will product these results.

=A1-WEEKDAY(A1,15)+8

I find interesting how many ways/methods can be used to arrive at a date no more than 7 days away. I'm almost ready to write a User Defined Function to return it. OK - here it is,

Function NextFriday(argDate As Range)

Dim dblDate As Double
    dblDate = CDbl(argDate)
    
    dblDate = dblDate + 1
    
    Do Until WorksheetFunction.Weekday(dblDate) = 6
        dblDate = dblDate + 1
    Loop
    
    NextFriday = CDate(dblDate)
End Function

I like NBVC's Signature which says: When there's a will there's many ways - Pick One

Originally Posted by MarvinP

=A1-WEEKDAY(A1,15)+8

Did you try that one, Marvin

Yep! Didn't it work for you?

One Test....

See attached for what I used...

ALTHOUGH
http://office.microsoft.com/en-us/ex...005209336.aspx - was limited in 2003?
http://office.microsoft.com/en-us/ex...010062298.aspx in 2007
http://excelsemipro.com/2010/09/the-...tion-in-excel/ in 2010

I see my assumption 2010 is the standard may lead to problems.

I knew you wouldn't ask if it worked for you. Thanks for the correction.

Originally Posted by MarvinP

Yep! Didn't it work for you?

D'oh! Apologies Marvin

......Excel 2010 only, though.....