Finding End Of Week Date

[Jasper]

I'm trying to set up a field that will "find" a week ending date (Fridays) that will display.

I already have a function showing the CURRENT DATE - with day of the week and date displayed - if the date is required in a calcuation field.

Any help, hints, tips would be appreciated.

JESSE

[-Queue-]

datefield + 6 - DayofWeek( datefield )

[Jasper]

Queue

I'm still having problems working out the solution to the Week Ending Date! For instance, I input your formula and today being May 25, the Week Ending Date should be Friday May 28, and your solution gives the date of May 5, 2004.

Any new suggestions? I am just not good with DATE calcs at all.

I tried your solution and get a date, however, it is not the weekending date for the week listed.

Here's how the system dates the days - on the left and how my new week starts out - on the right:

Sunday = 1 Saturday = 7

Monday = 2 Sunday = 1

Tuesday = 3 Monday = 2

Wednesday = 4 Tuesday = 3

Thursday = 5 Wednesday = 4

Friday = 6 Thursday = 5

Saturday = 7 Friday = 6

Friday, being payday, - hence Sat. being the first day of the new week, which means that it compounds the calculation.

I did get one to work about as close as yours, however, my date moves to different days also. My formula is IF(DayofWeek(datefield)=1,DayofWeek(Today) + 5, "Error")) - DayofWeek(Today) + 731724

I need to keep incrementing the date as today, May 25, I am getting the result of Thursday, May 20 as the Week Ending date.

Any help, comments, suggestions will be greatly appreciated.

JESSE

[RalphL]

datefield + 6 - DayofWeek( datefield ) should work for every day but Saturday. So try this

Case (DayofWeek( datefield ) = 7; datefield + 7; datefield + 6 - DayofWeek( datefield ))

[-Queue-]

Are you using a manually entered date field or a calculated date field of Status(CurrentDate)? If you're using the Status( ) one, the calc must be unstored or it won't update. Otherwise, there shouldn't be any problem with either formula.

Good catch, Ralph. I assumed that this would be a weekday only calc since the last day of the week is Friday.

[Jasper]

Thanks for the new inputs guys!

I am just using a normal field with the AutoEnterCalc set with the TODAY function as my primary date field and have it showing the DAY and DATE. My WeekEnding field uses that in its calculations.

I'll try a temporary fied and see if the CASE formula will work as needed, but I'll need to go a couple of days on it to proof it out. I'll get back with the results.

THANKS!!! JESSE

[-Queue-]

An auto-enter calculation won't update. You'll need to make it a true calculation field if you want it to update. Also, you'll want to use Status(CurrentDate) instead of Today, as Today will only update when you close and reopen the database, and make the calc unstored.

[Jasper]

Thanks a lot guys! I think I've got this working properly now.

I've incorporated the STATUS(CurrentDate) as suggested in the post prior to this

and it is giving me the dates I wanted so far. I'll keep checking it daily to make

sure the formulas are true.

JESSE