fmp19 Date Calculation

[TKnTexas]

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?

[comment]

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 March 18, 2023 by comment

[TKnTexas]

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. 

[comment]

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 March 19, 2023 by comment