11 Replies Latest reply on Jul 6, 2017 3:36 PM by arjen.evertse

# Add missing days to date range

I am trying to add days to the start date. So if Start date - End date equals a number. Would add one day 1 by 1 until it gets to the Start date - End date number.

• ###### 1. Re: Add missing days to date range

How many days?

FM dates are wonderful with math.

StartDate = EndDate - xNumberOfDays

Beverly

PS you have asked in Community Feedback and the @Discussions place has more people following it.

Sent from miPhone

• ###### 2. Re: Add missing days to date range

So if Startdate - Enddate = numberofdays say 5. Then how would you create a variable or loop to add StartDate + 1, StartDate +2. StartDate +3, StartDate+4, StartDate+5.

• ###### 3. Re: Add missing days to date range

Add it to what? A variable? A field? One new record for each date? Might this be a return separated list of dates?

All fit what you ask. The loop itself is very simple, but what happens inside that loop will depend on what result you want:

Loop

Set Variable [\$K ; \$K + 1 ]

Exit Loop If [\$K > NumberOfDaysHere -- this might be a constant, a value in a field or a value in a variable ]

Do something here depending on what you want

End Loop

• ###### 4. Re: Add missing days to date range

Do you need a series of dates?

StartDate

StartDate+1

StartDate+2

StartDate+3

StartDate+x (which equals EndDate)

?

May I ask why you need it so that I may reply with the best way to get these?

Beverly

Sent from miPhone

• ###### 5. Re: Add missing days to date range

If I understand you correctly, you would create a script with a loop that goes (End Date - Start Date) times.

So if you had a calculation like this:

GetAsDate("7/8/2017") - GetAsDate("7/1/2017")  --> assuming text fields, don't need GetAsDate with real date fields. = 7

So, that date subtraction calculation gives you seven times through the loop in your script.

Then, for each loop iteration, you would:

1. Add New record to the table

2. Set (the date) Field to the start date value (stored in a variable for easy access) PLUS the current loop counter to get successive dates.

So, the first new date field you add would be (using above example): GetAsDate("7/1/2017") + 1 = 7/2/2017

3. Do the next loop iteration, exit after 7 times.

Does this make sense or was that what you were asking?

• ###### 6. Re: Add missing days to date range

If you are looking for a way to generate a list of dates between your start and end date you might want to look into the DateRange custom functions here: FileMaker Custom Function:DateRange ( StartDate ; EndDate ; RangeLimit )

This returns a return separated list of dates. Note that you need FM Pro Advanced to be able to add custom functions to your solution.

• ###### 7. Re: Add missing days to date range

This helped. I set a variable before the loop as RateStart = \$NextDayAdd and then in the loop I set the variable as \$nextdayadd + 1 which works now. If I had FM Pro Advanced I would have went the custom function route. Thanks for all the replies. Was in a jam.

• ###### 8. Re: Add missing days to date range

Not sure what you are trying to do but let's assume it is FIND a range of dates.

This is easy.

\$nbrofdays = ?

\$startdate = your date

\$endate = \$startdate + nbrofdays

\$daterange = getasdate(\$startdate) & "..." & getasdate(\$endate)

enter find mode

set field date to \$daterange

perform find

• ###### 9. Re: Add missing days to date range

A list of sequential dates is also easy:

\$nbr = number of days

\$startdate = your date

set var \$list to \$startdate & PP

loop

set \$count to \$count+1

exit loop if \$count > \$nbr

set var set var \$list to \$startdate & PP & \$list

end if

I wrote that faster than I could find the website, lookup a custom function and etc...

...and test it for reliability

• ###### 10. Re: Add missing days to date range

Yes! good Custom Function if you have FMPAdvanced (to add/edit it). Here's an article on another:

"CustomList"

Beverly

1 of 1 people found this helpful
• ###### 11. Re: Add missing days to date range

Didn't know that one, thanks for sharing. Very powerful!