Calculating times past midnight

[Former Community Member]

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

Anybody please help me?

thanks

Gene-O

[radzmar]

Hi,

I changed the script to mention that break also can end past midnight.

[Former Community Member]

Radzmar,

Thanks so much this goes past midnight as promised. I have one more problem with it though....

Sometimes, an employee may only work in the morning (8am until noon) and take the rest of the day off with no break. I need your script to calculate those hours as a total and not wait for all of the fields (amIN, amOUT, pmIN, pmOUT) to be filled before reaching a total hours. Here is the script again, just in case you need it for the field names:

    
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
else
          $ = fullTime
endif

Can you  PLEASE put in something that will allow it to calculate after the amIN and amOUT fields are filled?

thanks so much

Gene-O

[radzmar]

I think, this will do the job.

[Former Community Member]

Radzmar,

I copy/pasted your script, and I can't get it to work. It calculates the total shift for all four fields, and it calculates past midnight, but I can't get it to calculate just the first two fields (amIN and amOUT) only. I pasted it into the calculate event in FormCalc.

Any ideas?

thanks so much

Gene-O

[radzmar]

???

I thought you told that the users sometimes only fills in amIN, when he only work before noon.

So I added the part

When you only fill out amIN it calculates the hours until 12:00.

If you like to enter anIN and anOUT then change the part into:

[Former Community Member]

I apologize if I didn't make myself clear! I am just not familiar enough with scripting to know where to insert your script to make it work.

Here is the latest script I have from you. WIll you please insert the addition to make it calculate amIN and amOUT if they are the only two entries?

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
else
          $ = fullTime
endif

THANK YOU SO MUCH!

Gene-O

[radzmar]

Here's a sample form for you.

https://acrobat.com/#d=bUWGhh9tZBvvSH6Bgv2nCA

[Former Community Member]

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!

HELP PLEASE!!!!

Gene-O

[radzmar · Accepted solution]

Ok,

I changed the script so it will check if amIN is larger the amOUT.

[Former Community Member]

You are AWESOME!

Thank you so much for your patience and working this out for me!!!

[sonyapatton123]

I have seen where you have helped many people can you please help me. I have no idea what I am doing.

I have three fields appt time, actual arrival time and a field for the difference in time.

How do I script the time to get difference. (Sometimes the actual arrival time can be the next day.)?