# fmp19 Date Calculation

Go to solution Solved by comment,

## Recommended Posts

I am working in the sql tables of the accounting software we use.  I added a field for the WEEK-NUMBER, using Friday (6) as the start.  I need to calculate what the corresponding date is.  I cannot figure it out.  Any suggestions?

##### Share on other sites

Posted (edited)
59 minutes ago, TKnTexas said:

I need to calculate what the corresponding date is.

I am afraid your question is not clear. What is the input to this calculation?

I should add that I don't really understand this part either:

59 minutes ago, TKnTexas said:

I added a field for the WEEK-NUMBER, using Friday (6) as the start.

Please explain in more detail, preferably with an example or two.

Edited by comment
##### Share on other sites

I am working in the shadow tables of our accounting system, specifically the Open AP transactions.  The table has Invoice Dates and Due Dates.  I added the calculated field for Week_Number = Year(DueDate)&"."&right("0"&WeekOfYearFiscal ( DueDate ; 6 ),2)  This works great.   I can sort on the Week_Number to get a total of invoices by the Friday that they will be due.  We write checks on Friday.

But Week_Number is a bit abstract, I want to pair it with the DATE of that Friday.

##### Share on other sites

• Solution
Posted (edited)

I am still not sure what are you looking for - and for what purpose.

If your week starts on Friday, and you want to group records by the week to which DueDate belongs to, you can calculate the date of the week's starting day (i.e. Friday in your case) using:

`DueDate - Mod ( DueDate - 5 ; 7 )`

Alternatively, you could just calculate an absolute week number as:

`Div ( DueDate + 2 ; 7 )`

though that's a pretty meaningless number you would not want to use for display.

1 hour ago, TKnTexas said:

I added the calculated field for Week_Number = Year(DueDate)&"."&right("0"&WeekOfYearFiscal ( DueDate ; 6 ),2)  This works great.

I don't think it does. Consider for example DueDate of Dec 31, 2012. Your calculation puts it in the 1st week of 2012 - while the correct answer would put it in the 1st week of 2013.

Edited by comment

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account

• ### Similar Content

• #### Is FileMaker a viable development platform anymore?

By Jeff M,

• 2 replies
• 111 views
• #### Summary of Text

By mhemans,

• 2 replies
• 91 views
• #### Dymo labelwriter 400 problem...

By heidi22,

• 0 replies
• 69 views
• #### Using Variable for Table Name in Set Field

By mdfOffman,

• 13 replies
• 180 views