5 replies to this topic

[Simon]

I'm working on a project for a client who wants to measure machine tool set time. I've created a Lotus Approach database for their engineers to enter changeover information direct into a computer for analysis. The problem is I have an equation that I am struggling; it has to do with decimals and time and how to convert - maths isn't my strongest point.

On the database I have 3 fields:

A - Start time (entered)

B - Finish time (entered)

C - Set Time (calculated) C = B - A


I want the answer to be expressed in minute's e.g.

When,

A - Start time = 9.05
B - Finish time = 14.10

I want the answer to be:

C - Set Time = 305 minutes

I've tried lots of calculations based around dividing the answer by .60 and .24 and stuff like that but nothing works for me. I'm sure it's possible but I sure can't do it.

Any help appreciated.

Thanks,
Simon

[M Greenaway]

I suggest you use MS Excel and it will do it for you !

However just think of the bit after the decimal point as 'sixtieths'.

So 9:05 would be 9 and 5 sixtieths (or 5/60) which is 9.083.

14:10 would be 14 and 10 sixtieths (or 10/60) which is 14.167.

Now simply subtract 14.167-9.083 = 5.084.

Then to convert to minutes multiply by 60 = 305.04 (drop the odd bits after the decimal point).

Ta da

[Simon]

Absolutely brilliant Martin!

I tried on MS Excel and yes it does it for you, but I couldn't see the maths behind it. The reason for using Lotus Approach is because it's the only database programme I own and I'm reasonably OK at using it. I wanted to use a database rather than excel because I can design a nice simple input form for the engineers to complete and lock the raw data away from their oily mitts. Of course I will have to export to excel for analysis.

Anyway I got it working. Firstly I sussed out how to format the start and finish fields to be time. Then I did a calculation (finish - start) on 1 hour and it gave me 360,000. I could see 60 would fit in there so I divided by 60 which gave me 6,000, divided again by 100 and Ta da.


The calculation: ("Run Time" - "Start Time") / 60 / 100

I've tested it with loads of different time combinations and it seems to work perfectly, I don't know how it just does. I would be interested if anyone could tell me how it works.

Thanks,
Simon

Attached Files

•  it_works.gif   53.84KB   53 downloads

[M Greenaway]

Now you have lost me !

PS Try using MS Access, its much better than Lotus Approach.

[Simon]

I'm lost too, but it works so no worries. Lotus SmartSuite came free with my PC a couple of years ago and Approach is the only software I use from it. I use excel and word mostly.

[Simon]

Below is the official answer I got from the Lotus help site. I'd arrived at the same thing myself but used /60/100 instead of /6000

As for the 2nd bit…well tool changes won't have to run into the next day - simple as that.

Regards,
Simon

RE: Lotus Approach 97 time calculations - answer expressed in minutes?
Posted by Sue Sloan on 15.Sep.05 at 09:07 using a Web browser
Category: Approach Release: All Releases Platform: Windows XP

1. Yes, it is entirely possible!
2. The start and end time fields are "time" type fields.
3. The tool change time field should be defined as "numeric" and not as "time". I would define it as 10.0.
4. If your start and end times are both within the same day, it is a simple subtraction and conversion to minutes. If the end time can be on a different day, it gets more complicated.

Concept: Time in Approach is stored as "hundreths of seconds" from zero at midnite. Subtracting the start time from the end time results in a number of hundreths of seconds elapsed between the two times. To convert to minutes you must divide by the right divisor:

ElapsedTime = Round((EndTime - StartTime)/6000)

Now to handle the case where the end time is in a different day, you need to have the starting day and ending day dates stored. One solution to this was posted a while back by Paul Bent on XpertSS.com:

====================================
First you need to get the startomg date and time into calculated fields as "numbers":

StartDateVal = Round(StartDate, 0)
StartTimeVal = Round(StartTime / 8640000, 9)
StartDateTime = StartDateVal + StartTimeVal

Concept:
- Rounding a date returns the number of days since 1-Jan-100 AD.
- Rounding a time returns the hundredths of seconds elapsed since midnight and dividing by 8640000 converts it to a fraction of 1 day.

Do the same calculations with EndDate and End Time to create EndDateTime. Then subtract to get days and fraction of days:

ElapsedDateTime = EndDateTime - StartDateTime

Here is an example to get a result in minutes with the fields in a database named JobOrder:

1) xCalledDateTime = Round(JobOrder.dCalled, 0) + Round(JobOrder.tCalled / 8640000, 9)
2) xStartedDateTime = Round(JobOrder.dStarted, 0) + Round(JobOrder.tStarted / 8640000, 9)
3) xResponseDateTimeInMinutes = Round((xStartedDateTime - xCalledDateTime) * 1440,0)

Sue Sloan
XpertSS.com