## Calculating times past midnight

Gene--O

Total Posts

76

Likes

3

0

Gene--O

Total Posts

76

Likes

3

0
Gene--O

29-11-2011

Hello All,

I am working on a timesheet for employees. It has 4 time fields (amIN, amOUT, pmIN, pmOUT) to allow for a lunch break. I have a script that calculates well for normal business hours. BUT, if the work shift goes past midnight, then it does not calculate correctly.

Here is what I have in FormCalc in a calculate event:

var fullTime = 0
var breakTime = 0

if (pmIN ne null and pmOUT ne null) then
if (Time2Num(amIN.formattedValue, "HH:MM") < Time2Num(pmOUT.formattedValue, "HH:MM")) then
fullTime = Abs(Time2Num(amIN.formattedValue, "HH:MM") - Time2Num(pmOUT.formattedValue, "HH:MM")) /(60 * 60 * 1000)
else
fullTime = 24 - Abs(Time2Num(pmOUT.formattedValue, "HH:MM") - Time2Num(amIN.formattedValue, "HH:MM")) /(60 * 60 * 1000)
endif

breakTime = Abs(Time2Num(pmIN.formattedValue, "HH:MM") - Time2Num(amOUT.formattedValue, "HH:MM")) /(60 * 60 * 1000)
\$ = fullTime - breakTime
else

fullTime = Abs(Time2Num(amIN.formattedValue, "HH:MM") - Time2Num(amOUT.formattedValue, "HH:MM")) /(60 * 60 * 1000)
\$ = fullTime

if (amIN == null) then \$=null else \$=(fullTime - breakTime)
endif

endif

thanks

Gene-O

View Entire Topic

Gene--O

Total Posts

76

Likes

3

0

Gene--O

Total Posts

76

Likes

3

0
Gene--O

12-12-2011

I'm sorry, but the sample does not do what I need the form to do!

Here is the script I have:

var fullTime = 0

var breakTime = 0

if (amIN ne null and pmOUT ne null) then

if (Time2Num(amIN.formattedValue, "HH:MM") lt Time2Num(pmOUT.formattedValue, "HH:MM")) then

fullTime = Abs(Time2Num(amIN.formattedValue, "HH:MM") - Time2Num(pmOUT.formattedValue, "HH:MM")) / (60 * 60 * 1000)

else

fullTime = 24 - Abs(Time2Num(pmOUT.formattedValue, "HH:MM") - Time2Num(amIN.formattedValue, "HH:MM")) / (60 * 60 * 1000)

endif

if (amOUT ne null and pmIN ne null) then

if (Time2Num(amOUT.formattedValue, "HH:MM") lt Time2Num(pmIN.formattedValue, "HH:MM")) then

breakTime = Abs(Time2Num(pmIN.formattedValue, "HH:MM") - Time2Num(amOUT.formattedValue, "HH:MM")) /(60 * 60 * 1000)

else

breakTime = 24 - Abs(Time2Num(pmIN.formattedValue, "HH:MM") - Time2Num(amOUT.formattedValue, "HH:MM")) / (60 * 60 * 1000)

endif

endif

\$ = fullTime - breakTime

elseif (amIN ne null and amOUT ne null and pmOUT eq null and pmIN eq null) then

\$ =  Abs(Time2Num(amOUT.formattedValue, "HH:MM") - Time2Num(amIN.formattedValue, "HH:MM")) / (60 * 60 * 1000)

else

\$ = fullTime

endif

There is only one problem with this script: it cannot handle shifts that start in the night hours and end in the early morning hours.

For example, if I input:                                                                      I get

amIN     amOUT     pmIN     pmOUT                                             TOTAL HOURS

20:00     02:00                                                                                          18

obviously this would be a 6 hour shift.

But if I input:                                                                                          I get

amIN     amOUT     pmIN     pmOUT                                             TOTAL HOURS

20:00     02:00          03:00     04:00                                                       7

which is correct.

I cannot figure out why the script won't work for the amIN and amOUT fields ONLY in this instance!