Date Question (WO Due date to "Week Of" OR Date Range

[Nestor]

Im having trouble inserting a date range and need some help.

I have a Word Order Due Date = 7/23/09

I created a report to see all my work orders by week so im able to get the field "work Orer due date" and turn it into Week of year which for the date of 7/23/09 is Week of year "30"

Unfortunately this number 30 doesn't mean a whole lot to a lot of people here so ist here a way to get a date range based on the Work order Due date OR the Week of year?

If possible i would like to have "7/20/2009-7/24/2009" OR week of "7/20/2009"

Any way of doing this..

thanks

[LaurenKuhlman]

Is this similar to your week of year calc?

Truncate (

DayOfYear (

GetAsDate ( "1/1/09" )

) / 7 ; 0

) + 1

Edited July 21, 2009 by Guest

[Nestor]

This is what i have for my week..

BUT my challenge is to be able to show the "Week of" that Monday of my "Week of year"

So in this case i would want to see "Week of 7/20/2009) Instead of Week 30

Hope this makes sense

Edited July 21, 2009 by Guest

[LaurenKuhlman]

Let ([



   firstDayOfYear = GetAsNumber ( Date ( 1; 1; Year ( Get ( CurrentDate ) ) ) );

   firstDayOfTheWeek = DayOfWeek ( firstDayOfYear );

   weekOfYear = WeekOfYear ( date )



];



   GetAsDate ( firstDayOfYear + ( weekOfYear * 7 ) - firstDayOfTheWeek - 6 )

   & " - " &

   GetAsDate ( firstDayOfYear + ( weekOfYear * 7 ) - firstDayOfTheWeek )



)

Dates are stored internally in FileMaker as the number of days since 1/1/0001. So, this is what my formula does. It gets the number of days between 1/1/0001 and the first day of the current year which is 733408 this year.

Then, I take your week number ( minus 1 ) times seven and add that to the first day of the year. This will return the number of the day of the week that was also the first day of the year. For example, the first day of the year this year was Thursday so it returns 733611 which corresponds with 7/23/2009 or Thursday of weekOfYear. Then, I subtract the DayOfWeek number and either add one or seven to get a Sunday through Saturday range. You can play with these numbers a bit if you want a different range.

weekOfYear.fp7.zip

[LaurenKuhlman]

I saw your note about formatting after I posted that. Here is the code to display Week of and the Monday of the week:

Let ([



   firstDayOfYear = GetAsNumber ( Date ( 1; 1; Year ( Get ( CurrentDate ) ) ) );

   firstDayOfTheWeek = DayOfWeek ( firstDayOfYear );

   weekOfYear = WeekOfYear ( date ) - 1



];



   "Week of " & GetAsDate ( firstDayOfYear + ( weekOfYear * 7 ) - firstDayOfTheWeek + 2 )



)

A note about this calculation: You must go to Storage Options... in the calculation dialog and check "Do not store calculation results -- recalculate when needed" since I called the Get ( CurrentDate ) function.

weekOfYear.fp7_2.zip

[Nestor]

WOW.... THANK YOU VERY MUCH...

THIS WILL REALLY HELP US OUT...

THANKS AGAIN.

[LaRetta]

I would handle it like this ... using a pop-up of valid Work Order Dates (but only Sunday's date). Let User select range then find the records for them (see attached). The other problem with WOY is that it can vary. Providing a range works really well.

For display, place a merge field at the top of your form with:

Week beginning: <>

cSundayDate is also used by the value list for Users to select from. Then select the merge field and format the date format to Sunday, August 2, 2009

UPDATE: DOH ... Of course you can search the c calculation itself instead of a range and the range would be +6 instead of +7. That's what happens when one is trying to finish posting when called into a meeting. :tongue2:

Find_Week.zip

Edited July 21, 2009 by Guest

[Nestor]

Another very good option....

Thanks Laretta