Extracting Start and End date out of Week

[Michael Allison]

Hey all,

This has got to be simple but I just can't figure it out. I've got a forecasting database that projects the total number of hours for the coming week. Basically I have a calculation that takes today's date and gives me the Monday of the next week. (My company forecasts from Monday - Sunday). So I can find the Week of Year for that Monday. I then want to find the Monday and Sunday of that week, the start and end date of that week. I was thinking this should work off the week number as this would take into consideration when the start of a new year starts in the middle of the week. Here is an example of what I'd like it to do:

Today: 5/22/03

Next Week's Monday: 5/27/03

Fiscal Week of 5/27/03: 51

Start Date of Week 51: 5/27/03

End Date of Weeek 51: 6/1/03

Any help would be appreciated.

Michael Allison

[Fenton]

Your basic calc is to find the Monday of a week; using a global date field, Date_g:

Date_g - Mod(Date_g, 7) + 1

Next Monday is obviously the above + 7.

I don't know how/why the week of the year enters into it. But you can get the week from the date WeekofYear(date). Or, alternatively, you can use the 1st day of the year, then add (Week number * 7) to end up on a date, then use the Monday calc.

[Michael Allison]

Nevermind...I figured out my own problem. I've posted it below to hopefully help others.

Here's the basics of what I have. The following uses a Monday-Sunday week.

Date: Today's date

Forecast Week Start date: Calculates next week's Monday. (7 * Int((Date + 7) / 7 ))+1

Monday of Forecast Week: Calculates Monday of Forcast Week Start Date in case user changes value. (7 * Int((Forecasting Week Starting Date + 7) / 7 ))-6

Sunday of Forecast Week: Calculates Sunday of Forecast Week. (Monday of Forecast Week +6)

Adjusted End Date: Calculates end of year If(Year(Sunday of Forecast Week) > Year(Forecasting Week Starting Date),TextToDate("12/31/"&Year(Forecasting Week Starting Date)), Sunday of Forecast Week)

I'm using this information on in a heading for the Forecasting portal. i.e. Forecast Period from [Forecast Week Start Date] to [Adjusted End Date]

Michael Allison