Dear folks.

I have a database which counts the minutes that are spend on
projects. So:

Start Time: 09:00
End Time: 10:45

Time Spent: 01:45

Now I need to manually fill in a field with: 1,75, so
Filemaker can add it up.

My brain will not allow me to come up with a formula to
convert the 01:45 to 1,75.
If it is 00:30 I can devide by 60 and will get the correct
0,5, but that does not work with 01:45 since Filemaker seems
to read that as 145.

I searched the internet including this group and also Excel
groups (since the problem could be similar) but could not
find the answer. Help is appreciated.

Steggy

Filemaker reads times as simple numbers (seconds). Thus 1:45 [timefield] =
6300 [numberfield]
Devide this by 3600 (seconds per hour) and you are left with 6300/3600=1,75

seconds per hour divided by seconds leaves your with the number of hours.
Thus 1:45 equals 1,75 hours.

Keep well, Ursus


"steggy" <steggy (AT) me (DOT) privacy.net> schreef in bericht
news:54t6mjF22dg3fU1 (AT) mid (DOT) individual.net...

In article <54t6mjF22dg3fU1 (AT) mid (DOT) individual.net>, steggy
<steggy (AT) me (DOT) privacy.net> wrote:

It depends what you're trying to do with the "added up" times, but you
probably don't need to convert times at all.

As long as all the appropriate fields are defined as *Time* fields or
Calculation fields returning a *Time* result, you can simply add or
subtract them. The usual problem here is caused by leaving one field as
a Number by mistake, often a Calculation field. You are almost
certainly already be doing that to get the Time Spent.
ie.
Time Spent = End Time - Start Time

This means you can get a Total Time Spent of the Found records using a
Summary field.

Total Time Spent Summary Field {Sum of Time Spent}


As another example, if a single record had two Start and End time
fields, you could have a calculation that gives the combined Time Spent
by simply adding them together.
eg.
Start Time 1 Time field

End Time 1 Time field

Start Time 2 Time field

End Time 2 Time field

Time Spent 1 Calculation field (Time result)
End Time 1 - Start Time 1

Time Spent 2 Calculation field (Time result)
End Time 2 - Start Time 2

Combined Time Spent Calculation field (Time result)
Time Spent 1 + Time Spent 2

An example record could be:

Start Time 1 = 9:00 End Time 1 = 10:45
Start Time 2 = 5:30 End Time 2 = 8:45

Time Spent 1 = 10:45 - 9:00 = 1:45
Time Spent 2 = 8:45 - 5:30 = 3:15

Combined Time Spent = 1:45 + 3:15 = 5:00


** BUT **
There is one major stumbling block that can occur here, and that is if
the Start Time and End Time are on two different days (ie. you go past
midnight), then the calculations will all be incorrect since they do
not take into account the change of day.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Helpful Harry wrote:

Harry!!! Great one. In the meantime I discovered that it
might be the fact that the field was not defined OK. Next
working day, would be Monday I guess, I will check that out.

But I was still struggling with the problem, and I think
your answer leads to the solution.

At first I was thinking it was a formula problem, but your
answer helps me out. Thanks again.

Sounds even easier



Thanks Ursus!


Ursus wrote:

Thanks so much Ursus. It worked great. Just did not realize
Filemaker converted the times to seconds. Big help.




Ursus wrote: