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

Calculate the end date by given a week no.


Recommended Posts

Hello,

 

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!

Damon

Link to post
Share on other sites
Robert Schaub

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 post
Share on other sites
WiderGates

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

 

Hawadääre

Dieter J.

Link to post
Share on other sites
CobaltSky

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 post
Share on other sites
  • 1 year later...
  • 3 years later...
  • 4 years later...
  • 7 months later...
  • 4 months later...
CobaltSky

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 post
Share on other sites
  • 6 years later...
AHunter3

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 post
Share on other sites

Join the conversation

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

Guest
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