9 Replies Latest reply on Oct 25, 2012 10:32 AM by jayGamel

# Date series calculations

### Title

Date series calculations

### Post

We publish 22 issues of a paper a year, publishing only a Jan 15 and a July 1 and on the 1st and 15th of every other month. I want a calculation that could

•           know today's date
•
•           increase the issue number by 1
•
•           change the day to "1" or "15"
•
•           change the year every jan 15

I'm having no luck whatsoever even getting started on this since dates throw me for a complete loop.

I have Issue_number, and Issue_date. I suspect it's easier to work with numbers and convert them to a specific date to do this, but  nothing has worked for me.

Any help gratefully accepted.

macintel - fmp 11.x

• ###### 1. Re: Date series calculations

Ignore the first two. THat's a simple serial number. its the dates that are stumping me.

• ###### 2. Re: Date series calculations

What determines whether the day will be 1 or 15?

Do you want to use the current date to determine that? Something like: If day is less than 15 return 1 of the preceding month, otherwise, return 15 of this month?

I would think that you'd want to determine this date from the record for the preceding issue instead of today's date.

• ###### 3. Re: Date series calculations

It's every 1st and 15th with two exceptions: Only July 1 and Only Jan 15. There is no July 15 or Jan 1 issue.

• ###### 4. Re: Date series calculations

I am assuming that you have one record for each issue.

THis script would take the date in the most recently created record and computes a date for the next issue, allowing for the two skipped dates, and then creates a new record with that info.

I will assume that your table is named Issues.

Show All Records
Sort Records [ No Dialog ; Restore ] //sort on IssueDate in ascending order
Go To Record/Request/Page [Last] // after sorting the most recent record will be the last record in the found set.
Set Variable [ \$Date ; Let ( [ isd = Issues::IssueDate ;
m = Month ( isd ) ;
d = Day ( isd ) ;
y = Year ( isd )
];
Case ( m = 12 and d = 15 ; Date ( 1 ; 15 ; y + 1 ); // issue date is for Dec 15th
m = 7 and d = 1 ; Date ( 8 ; 1 ; y ) ; // issue date is for July 1st
d = 1 ; Date ( m ; 15 ; y ) ; // issue day is the 1st
d = 15 ; Date ( m + 1 ; 1 ; y ) ; // issue day is the 15th
) //case
) // Let
]
New Record/Request
Set Field [ Issues::IssueDate ; \$Date ]

• ###### 5. Re: Date series calculations

I'm getting an error here (see pic), but I may be doing it wrong. I've never tried to construct a script this complex:

• ###### 6. Re: Date series calculations

Don't type in the final square bracket. It represnts the closing bracket in the typical set variable script step:

Set Variable [\$VariableName ; value: ExpressionGoes Here ]

It will be supplied by the script editor when you click OK on the two dialog boxes shown here.

• ###### 7. Re: Date series calculations

Phil,

Once again, you have been incredibly helpful. This works beautifully and it has been hugely instructive. I am taking a beginner course in Python (5 weeks in) and I'm starting to get what you're doing here. I'm guessing FM Case is like a 'while' in Python.

I owe you for this one (and many others). Let me know if you ever get up to wine country north of San Francico. (Sonoma County).

Jay

• ###### 8. Re: Date series calculations

I live in Central Cal (near Modesto) so you aren't terribly far away...

• ###### 9. Re: Date series calculations

I'm in Kenwood and you are welcome anytime. We have great food and wine and it's a great getaway destination. I can help find you a great place to stay, too.