TIME CARD

[Kathy]

Alpha V9

I tried all night to figure out how to subtract one time from another time to calculate the number of hours an employee worked.

Briefly the scenario:

TABLES:
1st table: Field name: Time Character: Time Field rule: Now()
2nd table: Field name: Time_Out Character: Time Field rule: Now()

TABLE SET:

I combined these two tables to a table set so I could make a report like this:

REPORT:

Employee Time Time_Out Hrs Worked
John 04/08/09 05:12:47 28 pm 04/08/09 07:12:58 67 pm ????

Where do I insert a calculation and what calculation formula do I use to convert the Time Character Fields to give me number of hours?

Sincerely, Kathy

[Stan Mathews]

Re: TIME CARD

I don't see how your fields can be character with a field rule expression of now(). Now() returns a time data value.

Time1-time2 = number of seconds. You just have to translate the seconds into hours and minutes.

[Kathy]

Re: TIME CARD

Yes, absolutely, I should have used the structure name "Type" instead of "Character". Very confusing.

I have tried that "time_out"-"time" (both of those are my field names) in:

1. as a field rule in the table set (creating a new field in one of those tables called "hours")

2. in the report as a new calculated field.

3. on events in the new field

All I get is a box looking item on the report.

I must be putting this calculation in the wrong place or do I need to "embellish" it depending on where I put the calcuation?

Where should I be putting the calculation?

Sincerely, Kathy

[Stan Mathews]

Re: TIME CARD

The expression would be

time_out-time without the quotes.

If you don't get an error but can't see/read the results, the object representing the results is not formatted with enough space to display it. Right click on the object, check the format tab.

Where should I be putting the calculation?

The report calculated field would be my choice.

[Kathy]

Re: TIME CARD

GROAN! I sort of remember back in those cobwebs about the formatting space! If this was the real issue after all those hours of trying different formulas! I'll let you know how this goes--not at that computer right now

Thank you, Kathy

[Mike Reed]

Re: TIME CARD

Hi Kathy,
Not sure what you are trying to do, but perhaps this will help for calculating hours:

I have a table with these fields (lots of others, too)

EID - character field, 7 - this holds the employee id number
Datebeg - date field
Timebeg - character field, 5
Dateend - date field
Timeend - character field, 5
hours - numeric 8,2


The hours field is a calculated field, using this function: THOURS(DATEBEG,TIMEBEG,DATEEND,TIMEEND)

The function thours() is user defined function with this as the code:

function thours as n(d1 as d,t1 as c, d2 as d, t2 as c)
'd1 is begin date as date field
'd2 is end date as date field
't1 is begin time
't2 is end time
'times are in 4 digit format plus AM or PM indicator. / is AM * is PM
'this is done to facilitate time entry using numeric keypad
' 9:05AM would be input as 0915/ 11:23PM would be input at 1123*
thours = (jdate(d2)-jdate(d1))*24 + (val(chours(t1,t2)))+ (if(sign((val(chours(t1,t2)))) = 0,1,sign((val(chours(t1,t2)))))*((val(substr(chours(t1,t2),at(":",chours(t1,t2),1)+1,2)))/60))
end function

Notice what the remarks are in this code. It says to data enter the time using this format:
4 digits plus either a / or a *. The forward slash and the star are both keys on the numeric keypad so the data entry can be done on the numeric keypad totally, without having to use the regular keys for the AM/PM stuff.
The / indicates the time as AM, the * is for PM. So as the notes say enter 9:15AM as 0915/ or 1:30PM as 0130* that makes data entry pretty fast.

your data file would look something like this:

EID DATEBEG TIMEBEG DATEEND TIMEEND HOURS
E000009 7/1/2008 1000* 7/1/2008 1100* 1.00
E000009 7/1/2008 1000* 7/1/2008 1100* 1.00
E000023 8/19/2008 1200* 8/19/2008 0400* 4.00
E000023 8/20/2008 1200* 8/20/2008 0345* 3.75
E000023 8/28/2008 0200* 8/28/2008 0315* 1.25
E000026 10/30/2008 0200* 10/30/2008 0500* 3.00
E000026 10/27/2008 1200* 10/27/2008 0130* 1.50
E000028 12/15/2008 0100* 12/15/2008 0330* 2.50
E000028 12/15/2008 0100* 12/15/2008 0100* 0.00
E000028 12/15/2008 0330* 12/15/2008 0345* 0.25
E000008 2/18/2009 0645* 2/18/2009 0800* 1.25
E000008 2/19/2009 0830/ 2/19/2009 0930/ 1.00
E000008 2/20/2009 0700* 2/20/2009 0800* 1.00


This will accurately calculate the hours down to the minute for you and make data entry easy. When printing out the times in a report, use these calculated fields for the output:

starttime = left(timebeg,4)+if(right(Timebeg,1)="/","AM",if(right(timebeg,1)="*","PM",""))
endtime = left(timeend,4)+if(right(Timeend,1)="/","AM",if(right(timeend,1)="*","PM",""))

[Bob Arbuthnot]

Re: TIME CARD

Kathy,

I do something similar to what you're doing in a time clock program I created.

When an employee clocks in I record now() in the clock_in field (long Time field). When they clock out I record now() in the clock_out field (also long Time).

Hrs_worked = clock_out - clock_in with hrs_worked being a calculated field.

It's that simple.

Bob Arbuthnot

[Kathy]

Re: TIME CARD

Oh boy I'm back and programming again.

Thank you ALL for replying. Stan suggested that perhaps I didn't have a long enough format in my report to know if my calculations were working or not. He was right.

But I learned alot in the wee hours and I really appreciate the support. It encourages me to want to program more and try harder things because I know there are people like each of you who are willing to share. Sometimes I can even come up with some suggestions but usually you guys are talking way above me!

Sincerely, Kathy