OT Calculation

[Gary_D__Cooper]

I've got a Form where I have a table with 8 fields and 1 field that calculates total hours worked. Each field the user needs to enter a valid time 8:00 AM or 1700, which will be converted to 5:00 PM. I'm trying to come up with a FormCalc formula that will give me the total hours worked.

This is what I have so far;

(Row5.Cell2-Row4.Cell2+Row7.Cell2-Row6.Cell2+Row9.Cell2-Row8.Cell2+Row11.Cell2-Row10.Cell2)*1440/60

The fields are as follows:

Start <Row4.Cell2>

Stop <Row5.Cell2>

Start <Row6.Cell2>

Lunch Start <Row7.Cell2>
Lunch End <Row8.Cell2>

Stop <Row9.Cell2>

Start <Row10.Cell2>

End <Row11.Cell2>

Total Hours

All it does is keep giving me weird totals and it's never consistant.

[radzmar]

Hi,

the calculation of time differences is not trivial.

A few rules to know:

1: For the total hours you should not use a date/time field - use a text field instead.

It's because the date/time field will only accept values within a 24 hours time frame.

Larger values will cause unexpected results.

2: The times are calculated in milliseconds.

3: The times needs to be converted from their formattedValue into milliseconds using the Time2Num() method to be comparable.

Here's a similar solution I posted a while ago.

You can adapt this to your own needs.

http://forums.adobe.com/message/4054814#4054814

[Gary_D__Cooper]

I tried to implement what you suggested, but now it just constantly shows a 0. Here is what I added to my FormCalc;

var fullTime = 0

if (Table1.Row4.Cell2 ne null and Table1.Row5.Cell2 ne null) then
if (Time2Num(Table1.Row4.Cell2.formattedValue, "HH:MM") lt Time2Num(Table1.Row5.Cell2.formattedValue, "HH:MM")) then
     fullTime = Abs(Time2Num(Table1.Row4.Cell2.formattedValue, "HH:MM") - Time2Num(Table1.Row5.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
    else
        fullTime = 24 - Abs(Time2Num(Table1.Row5.Cell2.formattedValue, "HH:MM") - Time2Num(Table1.Row4.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
    endif
endif

if (Table1.Row6.Cell2 ne null and Table1.Row7.Cell2 ne null) then
if (Time2Num(Table1.Row6.Cell2.formattedValue, "HH:MM") lt Time2Num(Table1.Row7.Cell2.formattedValue, "HH:MM")) then
     fullTime = fullTime + Abs(Time2Num(Table1.Row6.Cell2.formattedValue, "HH:MM") - Time2Num(Table1.Row7.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
    else
        fullTime = fullTime + 24 - Abs(Table1.Row7.Cell2(EndTime.formattedValue, "HH:MM") - Time2Num(Table1.Row6.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
    endif
endif

if (Table1.Row8.Cell2 ne null and Table1.Row9.Cell2 ne null) then
if (Time2Num(Table1.Row8.Cell2.formattedValue, "HH:MM") lt Time2Num(Table1.Row9.Cell2.formattedValue, "HH:MM")) then
     fullTime = fullTime + Abs(Time2Num(Table1.Row8.Cell2.formattedValue, "HH:MM") - Time2Num(Table1.Row9.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
    else
        fullTime = fullTime + 24 - Abs(Table1.Row9.Cell2(EndTime.formattedValue, "HH:MM") - Time2Num(Table1.Row8.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
    endif
endif

if (Table1.Row10.Cell2 ne null and Table1.Row11.Cell2 ne null) then
if (Time2Num(Table1.Row10.Cell2.formattedValue, "HH:MM") lt Time2Num(Table1.Row11.Cell2.formattedValue, "HH:MM")) then
     fullTime = fullTime + Abs(Time2Num(Table1.Row10.Cell2.formattedValue, "HH:MM") - Time2Num(Table1.Row11.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
    else
        fullTime = fullTime + 24 - Abs(Table1.Row11.Cell2(EndTime.formattedValue, "HH:MM") - Time2Num(Table1.Row10.Cell2.formattedValue, "HH:MM")) / (60 * 60 * 1000)
    endif
endif

$ = fullTime

[Gary_D__Cooper]

Maybe if I gave you an example it might help.

Start 8:00 AM

Stop

Start

Lunch Start 12:00 PM

Lunch End 1:00 PM

Stop

Start

End 5:00 PM

Total Hours 8.0

OR

Start 7:00 AM

Stop

Start

Lunch Start 12:00 PM

Lunch End 1:00 PM

Stop

Start

End 6:00 PM

Total Hours 10.0

[Gary_D__Cooper]

I've noticed that it might have to deal with the seconds. When you try to do calculations they need the seconds for it to work, but when a user enters 8:00 AM there's no way to add the 00 to the end of the 8:00. To do calculations you need it in the format of 8:00:00 AM.

[radzmar]

There is no need to enter the seconds to the time fields to calculate with times.

But it's neccessary to use the correct time pattern in the script.

The same mentioned above uses the pattern HH:MM to parse the formattedValue.

If your time fields uses another pattern like HH:MM A (01:23 AM) to display the time you have to change it in the entire script otherwise the returned value is always 0.

if (Table1.Row4.Cell2 ne null and Table1.Row5.Cell2 ne null) then

if (Time2Num(Table1.Row4.Cell2.formattedValue, "HH:MM A") lt Time2Num(Table1.Row5.Cell2.formattedValue, "HH:MM A")) ...

[Gary_D__Cooper]

I have done what you've suggested and the input fields are set to time with a format of time{h:MM A}. It seems to just not calculate with this.

var fullTime = 0

if (Table1.Row4.Cell2 ne null and Table1.Row5.Cell2 ne null) then
if (Time2Num(Table1.Row4.Cell2.formattedValue, "HH:MM A") lt Time2Num(Table1.Row5.Cell2.formattedValue, "HH:MM A")) then
      fullTime = Abs(Time2Num(Table1.Row4.Cell2.formattedValue, "HH:MM A") - Time2Num(Table1.Row5.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
    else
        fullTime = 24 - Abs(Time2Num(Table1.Row5.Cell2.formattedValue, "HH:MM A") - Time2Num(Table1.Row4.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
    endif
endif

if (Table1.Row6.Cell2 ne null and Table1.Row7.Cell2 ne null) then
if (Time2Num(Table1.Row6.Cell2.formattedValue, "HH:MM A") lt Time2Num(Table1.Row7.Cell2.formattedValue, "HH:MM A")) then
      fullTime = fullTime + Abs(Time2Num(Table1.Row6.Cell2.formattedValue, "HH:MM A") - Time2Num(Table1.Row7.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
    else
        fullTime = fullTime + 24 - Abs(Table1.Row7.Cell2(EndTime.formattedValue, "HH:MM A") - Time2Num(Table1.Row6.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
    endif
endif

if (Table1.Row8.Cell2 ne null and Table1.Row9.Cell2 ne null) then
if (Time2Num(Table1.Row8.Cell2.formattedValue, "HH:MM A") lt Time2Num(Table1.Row9.Cell2.formattedValue, "HH:MM A")) then
      fullTime = fullTime + Abs(Time2Num(Table1.Row8.Cell2.formattedValue, "HH:MM A") - Time2Num(Table1.Row9.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
    else
        fullTime = fullTime + 24 - Abs(Table1.Row9.Cell2(EndTime.formattedValue, "HH:MM A") - Time2Num(Table1.Row8.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
    endif
endif

if (Table1.Row10.Cell2 ne null and Table1.Row11.Cell2 ne null) then
if (Time2Num(Table1.Row10.Cell2.formattedValue, "HH:MM A") lt Time2Num(Table1.Row11.Cell2.formattedValue, "HH:MM A")) then
      fullTime = fullTime + Abs(Time2Num(Table1.Row10.Cell2.formattedValue, "HH:MM A") - Time2Num(Table1.Row11.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
    else
        fullTime = fullTime + 24 - Abs(Table1.Row11.Cell2(EndTime.formattedValue, "HH:MM A") - Time2Num(Table1.Row10.Cell2.formattedValue, "HH:MM A")) / (60 * 60 * 1000)
    endif
endif

$ = fullTime

[Gary_D__Cooper]

Any thoughts?

[radzmar]

Hi,

could you please upload your form anywhere and post a link to it here? It's hard to unserstand how your form is designed without seeing it.