WeekEnding Function 1

[DaleWatson123321]

The following thread was posted today in the VBA for Applications Forum... thread707-462398

Because there will be some MS Office members who don't frequent the VBA forum, I thought I should copy the posting I made. It reads as follows...


For those interested, here's an alternative (formula) that can be used without having to use VBA...

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

Additionally, if someone wants to format the cell to show the day-of-the-week, plus the date, use the following "Custom" Format... use these steps...

1) In the Format - Cells window, click the "Number" tab, and then choose "Custom".

2) Under "Type", enter: dddd - mmm/dd/yy

I should add: If you want to apply the formula against existing dates, simply replace the reference to "TODAY" with a cell reference. Then of course you can copy the formula down for as many dates as you have.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

I was a little hasty.

I should have made clear the PURPOSE of the "WeekEnding Function".

The purpose is to show the End-Date of the Week that any particular day falls in. In most cases the end-of-the-week is considered as SUNDAY, so the formula is set up to show the date for the following SUNDAY.

If the date being tested is SUNDAY, then the formula uses the SAME date.

I hope this clarifies the purpose. ...and I hope it can be of use.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlhelp]

Hi, Dale!

Would
=TODAY()+(7-WEEKDAY(TODAY(),2))
do the same? "Aliquando et insanire iucundum est"

 

[DaleWatson123321]

Yes, indeed !!!

I had thought of your method, but got sidetracked in my thought process I suppose.

Anyway, my hat is off to you, and a STAR

Regards, ...Dale