Calculate increments from start and finish time

[Jalz]

Hi Guys,

Need some advice from you experts out there.

By the way, im working with FM Pro 6v4 on Mac OSXv3, I have a database that contains the fields StartTime and EndTime.

The user can enter 9:00 as a start time and 10:30 as the end time from a dropdown list I have defined.

I need to define a calculated field which has the starttime and the endtime and also the increments of time in between by 15 minutes.

In our example starttime would be 9:00 and endtime would be 10:30 so hopefully the calculated field would display

9:00

9:15

9:30

9:45

10:00

10:15

10:30

Is this possible wihtout the use of a script?

Thanking all in advance

Jalz

[BobWeaver]

Would you ever have the situation where the start time and end time are on different days?

If not you could use the following:

1. Create a global text gTimeList field with the data:

"0:00

0:15

0:30

0:45

1:00

1:15

1:30

1:45

2:00

2:15

2:30

2:45

3:00

3:15

3:30

3:45

4:00

4:15

4:30

4:45

5:00

5:15

5:30

5:45

6:00

6:15

6:30

6:45

7:00

7:15

7:30

7:45

8:00

8:15

8:30

8:45

9:00

9:15

9:30

9:45

10:00

10:15

10:30

10:45

11:00

11:15

11:30

11:45

12:00

12:15

12:30

12:45

13:00

13:15

13:30

13:45

14:00

14:15

14:30

14:45

15:00

15:15

15:30

15:45

16:00

16:15

16:30

16:45

17:00

17:15

17:30

17:45

18:00

18:15

18:30

18:45

19:00

19:15

19:30

19:45

20:00

20:15

20:30

20:45

21:00

21:15

21:30

21:45

22:00

22:15

22:30

22:45

23:00

23:15

23:30

23:45

24:00

"

2. Create a calculated field called cIncrements with this formula:

Middle(gTimeList, Position(gTimeList, StartTime, 1, 1), Position(gTimeList, "

[Jalz]

Thanks Bob,

You are the Man!! Your calc works a treat.

if I wanted to extend it to contain the user name & 10:00, how could I do this?

i.e calc field revealing

jalz9:00

jalz9:15

jalz9:30

etc etc...

Thanks once again

Jalz

[BobWeaver]

You can run the result of the previous formula through a substitute() function like so:

UserName&Substitute (Middle(gTimeList, Position(gTimeList, StartTime, 1, 1), Position(gTimeList, "

[Jalz]

Thanks Bob,

That did the trick. Quick question, regarding relationships, is a ":" in a relationship a bad thing?

Thanks once again

Jalz

[BobWeaver]

Off the top of my head, I don't think the ":" should be a problem, but I tend to avoid colons as serparators and use hyphens or slashes instead. If the colon causes a problem, it's easy enough to leave it out of the gTimeList data altogether.

I should also mention that if you want to use the results of these formulae on the left side of a relationship there's no problem. But, to use it on the right side of a relationship, you will need to change gTimeList from a global field to a stored calculation field with its formula simply the time list text enclosed in quotes.