Jump to content

Calculate increments from start and finish time


This topic is 7474 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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, "

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 7474 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.