Calculating Times as Decimal Number

[Former Community Member]

Unfortunately, I've seen this question addressed several times, but never in the simple instructions needed for someone of my very novice abilities.

I am trying to create a Comp/Overtime request sheet, which requires a calculation of time fields much like a timesheet.



I have set up StartTime, EndTime, LunchStart, LunchEnd, and TotalHrs fields. The first four fields are set as date/time fields with HH:MM display pattern. The TotalHrs field is a numeric field. When I have them all listed as numeric fields, the scripting (EndTime-StartTime)-(LunchEnd-LunchStart) returns the correct value. However, once I change those fields to date/time type it returns a very strange number.



Can anyone help me to set this up? I've got a quickly approaching deadline and haven't been able to come up with any solutions. Any help would be greatly appreciated!

[gkaiseril]

You are getting a strange result for because the time of day is a very different item from the difference or accumulated time value. If one computes a 2 hour time difference a time field format would result in 2:00am while a 23 hour time would be 11:00pm and there is no concept for an elapsed time of 40 hours for a time a day. The result of time or date calculations should be return to a numeric field or plain text field in which the user creates the formatted string.



Since you are using LiveCycle Designer, I will assume you are using FormCalc and not JavaScirpt and your result is in seconds since midnight. You can then create variable to hold the result in various units of time measurement as whole numbers, but one will need to use the "Floor()" function to whole number from a decimal number and the "Mod()" function to remainder form a division.



So if one has a a value of 15300 seconds to get the total minute one divides by 60 and gets 255 minutes. One can now get the hours by dividing by 60 or 4.25 hours. Now to get the function hours, one can use the "Mod(n1, n2)" function to get remainder of the division of "n1" value by the "n2" value. So if one divides the number hours by 1 the remainder will be ".25" and 60 * .25 is 15 minutes. Now if one uses the "Floor(n1)" function to get the largest whole number from the value on "n1", one will get the value "4" when "Floor(4.25)" is used. Now to combine the various result strings and delimiter by using the "Concat(s1[,s2],...]])" function:



var dSeconds = 1530

var dMin = dSeconds / 60

var dHrs = dMin / 60

dMin = Mod(dHrs,1) * 60

dHrs = Floor(dHrs)

$.formattedValue = Concat(dHrs, ":", dMin)

[Former Community Member]

Geo -

if I use whole hour increments, the formula actually works out just fine. Whenever I use a half hour or quarter hour increment, i get very strange value returned.



For example, my clock times are 7AM, 11AM, 12PM, and 4PM - the value returned is 8 hours. If I change the clock times to 7AM, 11:30AM, 12PM, and 4 PM - the value returned is -3 hrs.



To refresh, I have 4 clock-in areas: StartTime, EndTime, LunchStart, and LunchEnd. I have a field called TotalHours in which I would like to tally the hours from the four clock times. In the calculation event of TotalHours, I have entered the following FormCalc formula:

Sum(LunchStart-StartTime)+(EndTime-LunchEnd)

In the exit event, I have the formula that you suggested above.



I know that the sum formula is working when only hours are entered because the software sees it as a numerical formula (11-7)+(16-12). Somewhere in here, I think I need to use the Time2Num function, but I don't understand how to phrase the formula.



Help.

[gkaiseril]

The date and time fields are string fields and not numeric fields because they contain the colon character and the optional meridian value. The date and time fields also do not add decimal numbers, they tend to be circular or get to a certain value and cycle back to 1. So one can not just add the field values together. One needs to transform the date or time string into a numeric value that represents a measure of time from a given point in time. Both Acrobat and LiveCycle Designer have a given base date, the Epoch date, form which the time interval is measured in milliseconds or days. Both products also provide some functions or methods to convert the date or time into this numeric value. Once one has the appropriate numeric value any number of time items or calculations can be performed.



First one needs to convert the time strings into a numeric value and the FormCalc function to provide this value is the "Time2Num()" function. This function returns the number of milliseconds, 1/1000 of a second, from the epoch date or start of the day if only the time string is provided.



So one can get the StartTime, formatted as "h:MM A", in milliseconds with:



Time2Num(StartTime.formattedValue, "h:MM A")



and the LunchStart time with:



Time2Num(LunchStart.formattedValue, "h:MM A")



And the difference for this intervals then



Time2Num(LunchStart.formattedValue, "h:MM A") - Time2Num(StartTime.formattedValue, "h:MM A")



And a similar coding will provide the time after lunch.



Adding some code to accumulate these periods and only compute an interval when the necessary variables are available one could have to get the time interval in milliseconds and then only needs to convert the milliseconds into hours by dividing by 3,600,000 milliseconds in one hour.



// compute time before lunch in milliseconds

var StartInterval = 0

if(HasValue(LunchStart) and HasValue(StartTime)) then

StartInterval = Time2Num(LunchStart.formattedValue, "h:MM A") - Time2Num(StartTime.formattedValue, "h:MM A")

endif



// compute time after lunch in milliseconds

var EndInterval = 0

if(HasValue(LunchEnd) and HasValue(EndTime)) then

EndInterval = Time2Num(EndTime.formattedValue, "h:MM A") - Time2Num(LunchEnd.formattedValue, "h:MM A")

endif



// compute total time in hours from the millisecond value

Sum(StartInterval, EndInterval) / 3600000