Generate dates from range

[Benzie]

Hi,

I wish to generate a list of dates from a range field which changes on a regular basis.

At present my range field shows 11/04/2003...21/04/2003

I now need to show all individual dates from that range in another field which includes the start and end dates. eg; 11/04/2003

12/04/2003

13/04/2003 and so on

preferably using a calc field as it is awkward running a script each time.

Manyt thanks

Benzie

[Pupiweb]

Get a range plugin ... there are several around, like Troi Ranges or Ranges ToolBox ...

[cjaeger]

depending on the number of days between the 2 days you could use a simple calc:

DatetoText(startDate) & "q|" &

if(EndDate>StartDate;DatetoText(StartDate +1) & "q|";"") &

if(EndDate>StartDate;DatetoText(StartDate +2) & "q|";"") &

if(EndDate>StartDate;DatetoText(StartDate +3) & "q|";"") &

....

q| stands for return char.

is of no use when you span several centuries.

To save you typing, yo may consider setting up a script which generates the calc

set field, g_temp to 1

set field calc to "DatetoText(startDate) & ""q|"" &"

loop

set field calc to "calc &" if(EndDate>StartDate;DatetoText(StartDate +g_temp) & ""q|"";"""") &"

set field g_temp to g_temp +1

exit loop if g_temp = 1000

end loop

go to next record

now copy calc into your calculation and delete the last "&"

[Benzie]

Hi Cjaeger,

Thanks for your response. I have tried the to make the script but get errors when pasting in your script steps - I have attached a small demo file of what i'm trying to achieve.Could you possibly have a look and advise?

Many thanks,

Benzie

daterange.zip

[BobWeaver]

Here is a sample file that does not require a script, just some calculation fields. Its limitation is that it will only generate a list of dates for a maximum span of 2 years. But, I assume that if you are trying to fit them into a text field, you probably don't need to have any more than that.

BTW, I should warn that this is set up for US date format, so you may have to modify the formula somewhat for DD/MM/YYYY format. I've included a script that initializes the gSequence global. It may be sufficient to simply run that script once to set up the global field to your system formats.

<<<Edited>>>

I noticed a bug in the file. So I replaced the original attachment. This one will work correctly with either an ellipsis or 3 periods as the date range separator.

Also, to adjust it to your local date format, Save the file as a clone and then open the clone and run the Gendates script. That should allow it to work anywhere.

DateRange3.fp5.zip

[Benzie]

Bob,

This is exactly what I require, and looking from the number of downloads will be of use to many people.

Many thanks,

Regards,

Benzie

[BobWeaver]

I just noticed another bug. I had it set to work when spanning more than one year when either year is a leap year, but I forgot the case where neither year is a leap year--trying to be too clever. I fixed this problem and reposted the file (DateRange3.fp5).

Better download it again.