auto weekly update of DATE 1

[5656]

Is there a simple formula that will automatically update a date on a weekly basis ? For example:
EVERY monday I would like the coming Friday's date to be displayed in a cell...
Is this possible ?

Thank you !

jf

 

[dneufarth]

=TODAY()+6-WEEKDAY(TODAY())

will set it to the next Friday for Sun - Fri; sat would be rolled back to previous Fri.

It would be assessed for each worksheet recalc though.


Dave

 

[5656]

I tried the above but didn't seem to work for what I need. I probably didn't explain very well exactly what I need...so here goes:
I would need the script to do the following:

By this coming Monday (Nov. 19) I would need my designated cell to display the FOLLOWING Friday's date (Nov. 30). I would like the cell to display Nov. 30 until Monday (Nov. 26) at which time I would need the cell to display Friday (Dec. 7).

Clear as mud ?

Thanks again !

jf

 

[tsuji]

Hello, 5656.

Suppose the Cell where the date is to place is at B4.

If I can continue the sage you've started in Thread68-165797, then you can append a line to auto_open():

Sub Autpen()
If WeekDay(Now()) = 1 Then
Range("B2:B3&quot.ClearContents
End If
Range("B4&quot.Value = Now() + 11 - (WeekDay(Now()) + 5) Mod 7
End Sub

The effect of this is to allow you not to put a formula in B4 which might be susceptible to be accidentally changed.

If you want to put the formula instead in B4, then it would be:

B4: = Now()+11-Mod(Weekday(Now())+5,7)

The cell B4 is required to be formatted to of date such as mm/d/yy or similar. Hrs:mins:secs would not then be displayed.

I had started with Now(), so I continue to use it in the above. You can also replaced it everywhere by Today() as suggested by neufarth. It does not really that much if you do not have further arithmetical manipulation on it.

regards - tsuji

 

[ETID]

Why not just this?


=NOW()+(6-WEEKDAY(NOW()))

then format to however you'd like.