Brian Dunning's FileMaker Custom Functions

WeekRange ( TheDate )

Gives the week date range (from monday to sunday) for a given date

  Average rating: 4.5 (28 votes) Log in to vote

Ibrahim Bittar Torres   Ibrahim Bittar Torres
Eikonsys, S.A. de C.V.
http://www.eikonsys.com

Share on Facebook Share on Twitter

  Sample input:
WeekRange ( Get (CurrentDate ) )
  Sample output:
11/07/2005...11/13/2005

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

This function returns the week range date for a given date. It´s ideal to search records for the current week.

For example: if today is 11/14/2005 and you want to find all the records in the week which 11/14/2005 belongs, you just have to use the function with the Get(CurrentDate) argument and it will return the week range 11/07/2005...11/13/2005.

You can use it in your search script using "Insert calculated Result" on any date field.

 

Comments

Katy   Katy, NZ
May 21, 2013
Hi

Thank you so much for posting this function. It saved my poor brain lots of work!

I suspect line seven needs to be altered to be
DayOfWeek ( Date ) = 7 ; Date - 5 ) ;
as I think Fecha must be a field name from your db?

Could be wrong!

Many thanks again.
 
Ibrahim Bittar   Ibrahim Bittar, León MX
May 21, 2013
Hi Katy

Thank you very much for noticing this. It's already fixed.

Ibrahim
 
Frank   Frank, CA
Feb 28, 2017
Is there a way that I apply this function over a range of dates..a start and end date and return a list of all the weeks within the range?

(Ultimately I need to be able to create in a table - and display data that allows the input of a number per day, with weekly & monthly sub-totals, i.e. order entry that will vary each time, some longer date ranges, some shorter)
 
Ibrahim Bittar   Ibrahim Bittar, León MX
Feb 28, 2017
Hi Frank

The purpose of this function is to set a date field in find mode. That's why I used the elipsis (...) between the two dates.

I suppose you can script a loop and create find requests for each date you have in your list and then perform a "big" find but I'm not sure if that's what you want.

Is that it?

Best regards

Ibrahim
 
richard   richard, uk
Jun 1, 2018
Hi there,

Forgive my ignorance, but I have been trying to use this script and I just can't seem to get it to work. ive copied and pasted into my fmp, but not sure how to actually use this? could you possibly post a step by step? sorry to bother you with this.
 
Ibrahim Bittar   Ibrahim Bittar, León GTO, Mexico
Jun 2, 2018
Hi Richard

This is not a script. It's a custom function and in order to implement it into your solution you must have FileMaker Pro Advanced.

You need to copy the text on the box (the actual function calculation) and then:

1.- Select File > Manage > Custom Functions... menu
2.- Create a new Custom Function.
3.- Function name is: WeekRange.
4.- Add Date as a parameter.
5.- Paste the calculation you copied here.
6.- Save and close.

After that you'll be able to use this custom function as any other regular functions in your calculations.

Hope you find this useful.

Best regards

Ibrahim
 
Ibrahim Bittar   Ibrahim Bittar, León GTO, Mexico
Jun 2, 2018
Made a simple update:

1.- Changed parameter "Date" by "TheDate" in order to avoid confusion with the Date ( ) function.
2.- Changed spaces by underscores into the Let function, just for peace of mind :).

It's much better to get used to never use spaces in calculations, field definitions or table names because it's a mess with ExecuteSQL.

Best regards

Ibrahim
 
richard   richard, uk
Jun 2, 2018
Hi Ibrahim, thank you so much for getting back to me.

Im trying to implement some kind of calculation that allows me to search for records within the current week. this seems to be the best one ive seen, and I can't work out anything better. I have used the following as a calculation

(Get (CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 2) & "..."&
(Get (CurrentDate ) + 7 - DayOfWeek (Get ( CurrentDate ) ) + 1 )

This doesn't work when I search on a Sunday.

the custom function won't allow me to enter date as a parameter for some reason. Also, how do I use the Get (currentDate) function with this? sorry to be a pain but my head is going crazy trying to work this all out
 
richard   richard, uk
Jun 2, 2018
Hi again Ibrahim, never mind I figured it out. I was doing it all wrong but thankfully with your help ive realised the error. thank you so much for this

Regards

Richard
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.