Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Calculate the end date by given a week no.


Recommended Posts



Is is possible to calculate the end date based on a user enter a week no.? If so, could you kindly suggest a calcuation formula?


Thank in advance!


Link to comment
Share on other sites

I know this is not quite what you want but here's another way.


2 Field =


day of week = Calc = Today


WeekEnding = calc =

Case( DayName(day of week) = "Sunday",Status( CurrentDate)+7,

DayName(day of week) = "Monday",Status( CurrentDate)+6,

DayName(day of week) = "Tuesday",Status( CurrentDate)+5,

DayName(day of week) = "Wednesday",Status( CurrentDate)+4,

DayName(day of week) = "Thursday",Status( CurrentDate)+3,

DayName(day of week) = "Friday",Status( CurrentDate)+2,

DayName(day of week) = "Saturday",Status( CurrentDate)+1,"")


Hope this helps , This figures out current week ending

Link to comment
Share on other sites

Year and Week are Fields.


Date of next Sunday:

Date(1 , 4 , Year) + Week * 7 - DayOfWeek(Date(1 , 4 , Year)) + 1


Date of next Monday:

Date(1 , 4 , Year) + Week * 7 - DayOfWeek(Date(1 , 4 , Year)) + 2



Dieter J.

Link to comment
Share on other sites

Hello Damon,

If I understand you correctly, what you want to do is enter a week number, and have the database calculate the date that that week number ends on, in the current year's calendar. I also assume, since you haven't said otherwise, that you are regarding week 1 as being the week in which 1 January falls (which is how week numbers are defined for the purposes of FileMaker's WeekofYear( ) function) and weeks as starting on Sunday and ending on Saturday (which is how weeks are defined for the purposes of FileMaker's DayofWeek( ) function).


That being the case, you should use a calculating date field with a formula along the lines of:


WeekNumber * 7 + Date(1, 1, Year(Status(CurrentDate))) - DayofWeek(Date(1, 1, Year(Status(CurrentDate))))


Where 'WeekNumber' is a number field into which the number of the week is being entered.


The dates that the formula will produce will always be a Saturday - and will be the Saturday which occurs at the end of the WeekNumber week.


If you want to find the date of a different day within the WeekNumber week, you could subtract the relevant number of days from the above formula. So, for instance, to always find the date of the Friday of the WeekNumber week, append "- 1" to the above formula. wink.gif

Link to comment
Share on other sites

  • 1 year later...
  • 3 years later...
  • 4 years later...
  • 7 months later...
  • 4 months later...

In fact, in versions of FIleMaker since 7 (released in 2004), a better way to write this expression has been available:


Let(d1 = Date(1, 1, Year(Get(CurrentDate))); WeekNo * 7 + d1 - DayofWeek(d1))


The logic is the same, but the use of the Let( ) function to store the first day of the year as a variable (which I've called "d1" in the example above) saves having to calculate it twice, as was necessary in prior versions.


Other than that, it's good to see that the code has helped a few people over the intervening eleven years or so. :)

Link to comment
Share on other sites

  • 6 years later...

TimeStamp, Date, and Time are all recorded under the hood as serial numbers.   So you don't create the day name in the timestamp, you simply format it to display with the day name wherever you've placed it on a layout.  The presence or absence of slashes or dashes, spelled-out monthnames, daynames, and so forth are not in the fields, they are just expressions of the serial number's meaning.  

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

Terms of Use