Time calculations past midnight

[Former Community Member]

Hello All,

I have created a timesheet that calculates times for me. BUT I have a problem when the shift ends at midnight, or goes past midnight.

I have 4 time fields: amIN, amOUT, pmIN, pmOUT, and then a Total Hours Field.

Everything works fine up until midnight.

I have the follwing script in FormCalc as a calculate event of the Total Hours Field (taken from an example previously sent to me:

var amIn = Time2Num(amIN, "HH:MM")

var amOut = Time2Num (amOUT, "HH:MM")

var pmIn = Time2Num (pmIN, "HH:MM")

var pmOut = Time2Num (pmOUT, "HH:MM")

var vTotal = 0

if (amIn <> "" and amOut <> "") then

     if (amOut lt amIn) then

          xfa.host.messageBox("The morning times are inccorect", "oops", 0,0)

     else

          vTotal = (amOut - amIn) / (60 * 60 * 1000)    

     endif

if (pmIn <> "" and pmOut <> "") then

     if (pmOut lt pmIn) then

          xfa.host.messageBox("The afternoontimes are inccorect", "oops", 0,0)

     else

          vTotal = vTotal + (pmOut - pmIn) / (60 * 60 * 1000)    

     endif

$ = vTotal

This works great, but if you go up until midnight, say a 3:00pm - 12:00am shift, then you get the error. I need this to calculate including, or even past midnight, ex. a 5:00pm - 2:00am shift.

thanks for any and all help!!

Gene-O

[radzmar]

Hi,

here's a FormCalc script to calculate the difference between two times no matter if am to pm or pm to am.

[Former Community Member]

Radzmar,

This is very helpful!!! BUT, it does not take into account a break for lunch. I have to have amIN, amOUT, pmIN, and pmOUT fields.

Can you adapt what you have given me to account for a lunch break that may be more than one hour long?

thanks

Gene-O

[radzmar · Accepted solution]

You only need to change the formula a little bit.

[Former Community Member]

Awesome!!!

thank you so much!

[Former Community Member]

I’m sorry, but I have one more issue to deal with:

As I’ve said, I have “amIN”, “amOUT”, “pmIN” and “pmOUT” fields. If I enter values in “amIN” and “amOUT” only, then I get an incorrect sum for total hours.

For example:

It is possible that someone may work from 8am (amIN) until 12pm (amOUT) and then take leave the rest of the day. I need the script to then only total the “amIN” and “amOUT” fields for the total hours of 4.

Will you help me one more time please?

Thanks!!!

Lt. Gene Schrieber

Alabama Alcoholic Beverage Control Board

Enforcement District 1

P.O. Box 1755

Florence, Alabama 35631

(256) 764-2435

(256) 766-1793 Fax

www.abc.alabama.gov

"It's not about where you've been, it's where you are going that matters"

[radzmar]

No problem, just add one if expression for this purpose.

[Former Community Member]

Thank you thank you thank you thank you!

Lt. Gene Schrieber

Alabama Alcoholic Beverage Control Board

Enforcement District 1

P.O. Box 1755

Florence, Alabama 35631

(256) 764-2435

(256) 766-1793 Fax

www.abc.alabama.gov

"It's not about where you've been, it's where you are going that matters"

[Former Community Member]

Radzmar,

I’m sorry to bother you again, BUT, I have run into a problem. In certain instances, the calculation does not handle crossing midnight well at all. I have attached a copy of the form so you can see the results.

Please note on Thursday the 3rd, and Friday the 4th, I am getting a negative hour value return.

Will you please help me again!

Thanks!

Lt. Gene Schrieber

Alabama Alcoholic Beverage Control Board

Enforcement District 1

P.O. Box 1755

Florence, Alabama 35631

(256) 764-2435

(256) 766-1793 Fax

www.abc.alabama.gov

"It's not about where you've been, it's where you are going that matters"

[CJ54235]

Hi Radzmar!

I am a newbie to writting FormCalc. I have tried to tweak your sample to fit my needs with no luck.

I have 6 fields for each weekday - in & out times for morning, afternoon, and overtime. I haven't even attempted to look at how overtime is calculated. Right now I am just trying to get the hours calculated for morning and afternoon minus the lunch break. I have attached a link to my feeble attempt in the hopes you could look at it.

http://www.whitetailsunlimited.com/i/p/TimeSheet20121203.pdf

Thanks much in advance!

CJ