Time

[Annette Marie]

Was wondering if I could get help with a calculation.

I have the following fields in a database in a line items/portal thingie (very technical term I know :-P ):

Date--Day--Time In--Time Out--Total Hours

Basically I want to enter the time a person came in, the time they left and the total hours that they were there. It's simple enough if the hours are within the same day...but how do I get hours to calculate if it went over into the next day judging by the time I entered.

Ex. So I enter the following

Date: 14/08/10 -- Day: Saturday -- Time in: 10:00 -- Time Out: 9:00 Hours: 23

i.e. -

I came in at 10 am on Saturday and left on 9 am on Sunday....so total hours should say 23 hours.

(the times will be entered in military time to tell the difference between am/pm)

[Annette Marie]

Is this something I should be adding a second date field to the line so the date of time out can be entered as well??

[comment]

It depends on whether a person can stay longer than 24 hours. If not, you can assume that if Time Out is less than Time In, it's the next day.

[LaRetta]

Since you provide an example of 23 hours, I'd worry that it indeed COULD go over 24. Regardless, the simplest and safest way is to use timestamps (for TimeIn and TimeOut) instead. Then your TotalHours calculation (result is time) would be simple TimeOut - TimeIn.

Have you considered button which simply sets each field with Get(CurrentTimeStamp)? If someone forgets to clock in or clock out, then Admin will need to correct their time but that would be required no matter what solution you choose.

There also is no need for the Day field because you can simply place the TimeIn field on a layout and format it Inspector > Data tab at bottom. Turn off entry to the field or use merge field instead. Specify your date formatting (select calendar) as Custom. You can leave the first entry on the day name and change all other pop-ups to None.

[Lee Smith]

I agree with LaRetta about using of Timestamps if you can,

If you can't do that (as in too late),

(((Date_End - Date_Start) * 86400) + Time_End - Time_Start) /3600

or

Let(

  [

    days = ((Date_End-Date_Start) * 86400);

    mins = Time_End - Time_Start

  ];



  (days + mins)  / 3600

)

or if you don't have the second date field, then comment's suggestion of checking to see if the start time is greater then then the end time, you could calculate it something like this

Let(

  [

    days = Case (Time_End < Time_Start ; 1) * 86400;

    mins = Time_End - Time_Start

  ];



  (days + mins)  / 3600

)

HTH

Lee

Corrected a typo in last Let calculation.

Edited August 21, 2010 by Guest

[Annette Marie]

OK. It may help if I try to explain what I'm doing.

Basically I am trying to create a database to record the services clients are receiving, some by more than one provider. At the end of the month the provider enters the clients service details; the date they attended a service, the times, if they spent the night, and even record whether a scheduled service was cancelled by either the client or the provider.

A person can stay for an entire month, although this is rare. But often they may arrive on a Friday morning and stay until Monday morning.

If they are staying for a period of time without leaving, they are only there for the one service. But, it could be that they showed up on Monday for one service, went home, came back Wednesday for another service, went home, etc.

So ideally for each line I would like to enter one visit, no matter how long that one service visit was for. I.e. On one line enter that I arrived on Monday at 10 am for outreach service, and was there until Wednesday at 10 am. On the next line enter that I arrived on Friday at noon for tutoring and left Friday at 2 pm. Etc.

Since I need to keep track of if the client stayed overnight and for how many overnights they attended in a month I'm not sure if the above is possible. I might have to do each day in separate lines as I have it at the moment and just have a check box if they stayed overnight. Then I can add a calculation to the form to add the number of overnights checked.

I know that was REALLY long winded, but I figured if I explained all the different info going into this and needed from it, that it might help others to understand what I'm trying to accomplish.

I'll attach an updated version of the database. The page I'm referring to is the Record Detail - Service Hours form.

Respite_Clone.zip

[comment]

ideally for each line I would like to enter one visit, no matter how long that one service visit was for. I.e. On one line enter that I arrived on Monday at 10 am for outreach service, and was there until Wednesday at 10 am.

I don't see why they couldn't enter exactly that (and not much else).

As for overnights, it depends on how they are defined. In the above example, one could assume two overnights - but what about a service from 1:00 am until 5:00 am? If the overnight aspect cannot be calculated from the in and out dates and times, then the provider should enter a number in the overnight field instead of a Boolean.

You also need to decide if a visit that began on the 31th of a month and lasted for 2 days belongs to the starting or ending month.

---

P.S. I believe you are doing yourself a disservice with that template; it looks very nice on the outside but needs a lot of cleaning up under the hood.

[Annette Marie]

My problem being that I am so new to this I wasn't sure where to begin to start from scratch. I started with a template and altered as I go. My problem will be relationships and such.

And your calculation of an overnight is correct.

At the moment I am recording all this info on a RIDICULOUS excel sheet. Hours are broken down into two twelve hour shifts for the service that would incorporate an overnight. 8pm - 8am is an overnight. 8 am - 8pm are just calculated by individual hours and referred to as "day hours" in the current recording of them in excel.