Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Calculating times past midnight

Avatar

Level 4

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

1 Accepted Solution

Avatar

Correct answer by
Level 10

Ok,

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

View solution in original post

11 Replies

Avatar

Level 10

Hi,

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

Avatar

Level 4

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

Avatar

Level 4

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

Avatar

Level 10

???

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:

Avatar

Level 4

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

Avatar

Level 4

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

Avatar

Correct answer by
Level 10

Ok,

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

Avatar

Level 4

You are AWESOME!

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

Avatar

Level 1

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.)?