Find Mondays Date???

[blackcat]

I have a number field in each record that is manually entered that denotes which week in the year a process needs to be completed.

 

When I search by that week number I want to display the Date in a global field for each day.

 

eg Week No is 8

Monday(global) =18-2-02

Tuesday(global)=19-2-02

etc

 

Any easy way?

 

Steve

[andygaunt]

Blackcat, your email address if you please, then can send you some files we use.

 

Will explain on list later.

[andygaunt]

Thought I would share with the list what I proposed, as it may benefit others, or someone may have a twist on this idea.

 

Very difficult to set 7 global fields to a specific date based only on a week number.

 

So, if you have a dates db with 1000's of dates in you can use this to get the functionality.

 

In the dates db you have a date field, a calc field to tell you the

 

day of week:

DayofWeek(Date)

A week number field:

WeekNumber

WeekOfYear(Date)

The weekNumberWithYear

(calc, text) WeekNumber & ":" & Year(Date)

 

and a calc to bring it all together as a

 

Record ID

DayofWeek( Date ) & ":" & TheWeekNumberWithYear

 

This can then be used to calculate the global dates.

 

In the database you want the specific dates, you have the following

 

WeekNumber field (global number)

DayOfWeek (global number)

WeekNumberWithYear (calc, text)

week number &":"& Year(Status(CurrentDate))

DayOfWeek_WeekNo_Year (calc, text)

dayofweek &":"& WeekNumber &":"& Year(Status(CurrentDate))

 

OK, Now the relationships.

 

Two required. both to your preset dates db

 

1 Show Dates= WeekNumberWithYear::WeekNumberWithYear

2 GetDate= DayOfWeek_WeekNo_Year::RecordID

 

Phew, well we are nearly there I promise. I said this needed some explaining.

 

Now the script(s)

 

1. Set the day of week

 

Set DayOfWeek (DayOfWeek) + 1

 

2. Set the Global Dates.

 

GTRR (Show, Show Dates)

Set Field (DayOfWeek, 1)

Set Field (Sunday.gl, GetDate::Date)

PerformScript (Set the day of week)

Set Field (Monday.gl, GetDate::Date)

PerformScript (Set the day of week)

Set Field (Tuesday.gl, GetDate::Date)

PerformScript (Set the day of week)

Set Field (Wednesday.gl, GetDate::Date)

PerformScript (Set the day of week)

Set Field (Thursday.gl, GetDate::Date)

PerformScript (Set the day of week)

Set Field (Friday.gl, GetDate::Date)

PerformScript (Set the day of week)

Set Field (Saturday.gl, GetDate::Date)

PerformScript (Set the day of week)

Set Field (DayOfWeek, "")

 

Phew, done!.

 

So, depending on the week of the year you set, by clicking a button you can populate seven global fields with the relevant dates.

[Doug]

Another possible solution to find the Monday of the End Process week... keep in mind Filemaker starts the week on Sunday!

I have tweaked this solution to give the Monday date, very easy to change.

 

Create these fields:

 

Current Date = Status(CurrentDate)

 

Current Week = WeekofYear(Current Date)

 

End Process Week = your manually entered process end week

 

MondayofCurrent Week = Case( DayName(Current Date) = "Monday" , Current Date ,

DayName(Current Date) = "Tuesday" , Current Date - 1 ,

DayName(Current Date) = "Wednesday" , Current Date - 2 ,

DayName(Current Date) = "Thursday" , Current Date - 3 ,

DayName(Current Date) = "Friday" , Current Date - 4 ,

DayName(Current Date) = "Saturday" , Current Date - 5 ,

DayName(Current Date) = "Sunday" , Current Date + 1,

"")

 

Mondayof EndWeek = MondayofCurrent Week + ((End Process Week - Current Week) * 7)

 

I am assuming you are not crossing into the next or previous year, if you are, you will need to factor that into the equations (sample available).

Please note, that in this example, if your end week is prior to the current week [or empty] the date calculates backwards from the current monday.

 

Now that you have the monday of the End Process Week, you can fill in the rest of the days.

Also, you can combine some of the above to save a field or two.