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?

Damon

##### 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

##### 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.

##### 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.

##### Share on other sites
• 1 year later...

Great!! Just what I was looking for :-)

/Magnus

##### Share on other sites
• 3 years later...

Just what I was looking for too, 4 years later. Thanks.

##### Share on other sites
• 4 years later...

In FM 12

(Week * 7 + Date(1; 1; Year(Get ( CurrentDate )))) - DayOfWeek(Date(1; 1; Year(Get ( CurrentDate ))))

##### Share on other sites
• 7 months later...

Exactly what I was looking for. Thanks everyone!

In FM 12(Week * 7 + Date(1; 1; Year(Get ( CurrentDate )))) - DayOfWeek(Date(1; 1; Year(Get ( CurrentDate ))))
##### Share on other sites
• 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.

##### Share on other sites
• 6 years later...

how to create day name (monday - sunday) in timestamp? im new in filemaker, thanks

##### Share on other sites

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.

## Join the conversation

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

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

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

×

• ### Images

• 0
By Soliant Consulting,
• 0
By Soliant Consulting,
• 0
By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33,435
• Total Posts
141,242
×
×
• Create New...