Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.

## Timesheet calculations - Where am I going wrong?

Level 2

I've been batting this one back and forth for weeks now and every time I think I've finally nailed it something breaks it.

Entering different values can cause terrible trouble for the calculations.  It's all based on a cheat's way of calculating time by using left and right to capture the values for hours and minutes and then using them in a calculation before glueing them together using concatenation.

It seems to handle minus figures up to a point and when I halve the standard working hours things can go very wrong.

Here's a full list of the scripts used.  I'd be grateful if someone could help identify what I need to do to catch and fix these anomalies.

Calculating a Working Day

if      (HasValue(form1.flow.page.parent.part2.part2body.timesheet.Mon.endPM) & HasValue(form1.flow.page.parent.part2.part2body.timesheet.Mon.startAM))

then

//      Compute time difference in minutes

var     TimeDiff = ((Time2Num(form1.flow.page.parent.part2.part2body.timesheet.Mon.endPM.formattedValue, "H:MM") - Time2Num(form1.flow.page.parent.part2.part2body.timesheet.Mon.startAM.formattedValue, "H:MM")) / (1000 * 60)

- (Time2Num(form1.flow.page.parent.part2.part2body.timesheet.Mon.startPM.formattedValue, "H:MM") - Time2Num(form1.flow.page.parent.part2.part2body.timesheet.Mon.endAM.formattedValue, "H:MM")) / (1000 * 60))

//      truncate to hours

var     HourDiff = Floor(TimeDiff/60)

//      get minutes less than 60

var     MinDiff = Mod(TimeDiff,60)

//      build formatted string

var     netTime = concat(Format("Z9", HourDiff), ":" , Format("99",MinDiff))

//var     Final = concat((HourDiff), ":" , (MinDiff))

elseif      (HasValue(form1.flow.page.parent.part2.part2body.timesheet.Mon.startAM) & HasValue(form1.flow.page.parent.part2.part2body.timesheet.Mon.endAM) & (form1.flow.page.parent.part2.part2body.timesheet.Mon.endPM.rawValue==null))

then

//      Compute time difference in minutes

var     TimeDiff = ((Time2Num(form1.flow.page.parent.part2.part2body.timesheet.Mon.endAM.formattedValue, "H:MM") - Time2Num(form1.flow.page.parent.part2.part2body.timesheet.Mon.startAM.formattedValue, "H:MM")) / (1000 * 60))

//        - (Time2Num(form1.flow.page.parent.part2.part2body.timesheet.Mon.startPM.formattedValue, "H:MM") - Time2Num(form1.flow.page.parent.part2.part2body.timesheet.Mon.endAM.formattedValue, "H:MM")) / (1000 * 60))

//      truncate to hours

var     HourDiff = Floor(TimeDiff/60)

//      get minutes less than 60

var     MinDiff = Mod(TimeDiff,60)

//      build formatted string

var     netTime = concat(Format("Z9", HourDiff), ":" , Format("99",MinDiff))

//var     Final = concat((HourDiff), ":" , (MinDiff))

else

//   if any values missing null the output

null

endif

Calculating Weekly Hours

var MonMin = Right(form1.flow.page.parent.part2.part2body.timesheet.Mon.monTotal, 2)

var TueMin = Right(form1.flow.page.parent.part2.part2body.timesheet.Tues.tueTotal, 2)

var wedMin = Right(form1.flow.page.parent.part2.part2body.timesheet.wed.wedTotal, 2)

var thurMin = Right(form1.flow.page.parent.part2.part2body.timesheet.thur.thurTotal,2)

var friMin = Right(form1.flow.page.parent.part2.part2body.timesheet.fri.friTotal,2)

var TotalMin = sum(MonMin, TueMin, wedMin, thurMin, friMin)

var MonHour = Left(form1.flow.page.parent.part2.part2body.timesheet.Mon.monTotal,2)

var TueHour = Left(form1.flow.page.parent.part2.part2body.timesheet.Tues.tueTotal,2)

var WedHour = Left(form1.flow.page.parent.part2.part2body.timesheet.wed.wedTotal,2)

var ThuHour = Left(form1.flow.page.parent.part2.part2body.timesheet.thur.thurTotal,2)

var FriHour = Left(form1.flow.page.parent.part2.part2body.timesheet.fri.friTotal,2)

var TotalHour = sum(MonHour, TueHour, WedHour, ThuHour, FriHour)

var Minutes = Mod(TotalMin,60)

var FinTot  = floor((TotalMin / 60 )) + (TotalHour)

var MinRem

if (Minutes < 10) then

MinRem = concat("0", Minutes)

else

MinRem = Minutes

endif

\$.rawValue = concat(FinTot, ":", MinRem)

Calculating Hours Gained or Lost over the Week

var item1 = ((left(form1.flow.page.parent.part2.part2body.timesheet.total.weekTotal, 2)) + (left(form1.flow.page.parent.part2.part2body.authAbs,2)))

var item2 = ((right(form1.flow.page.parent.part2.part2body.timesheet.total.weekTotal,2)) + (right(form1.flow.page.parent.part2.part2body.authAbs,2)))

var itemA = left(form1.flow.page.parent.part2.part2body.std.stdHours,2)

var itemB = right(form1.flow.page.parent.part2.part2body.std.stdHours,2)

var hourDiff = (item1 - itemA)

var minDiff  = (item2 - itemB)

var final

if      ((hourDiff < 0) and (item2 > 0))

then    hourDiff = ((hourDiff)-- 1)

minDiff = (60 - item2)

final = concat(hourDiff, ":", minDiff)

elseif  ((hourDiff > 0) and (item2 > 0))

then    final = concat(hourDiff, ":", minDiff)

elseif  ((hourDiff > 0) and (item2 == 0))

then    final=concat(hourDiff,":","0",minDiff)

endif

\$.rawValue = final

Calculating the Total Number of Hours worked over the four week period

var W1Min = Right(form1.flow.page.parent.part2.part2body.timesheet.total.weekTotal, 2)

var W2Min = Right(form1.flow.page.parent.part3.part3body.timesheet2.total.weekTotal, 2)

var W3Min = Right(form1.flow.page.parent.part4.part4body.timesheet3.total.weekTotal, 2)

var W4Min = Right(form1.flow.page.parent.p5.part5body.timesheet3.total.weekTotal,2)

var TotalWMin = sum(W1Min, W2Min, W3Min, W4Min)

var W1Hour = Left(form1.flow.page.parent.part2.part2body.timesheet.total.weekTotal,2)

var W2Hour = Left(form1.flow.page.parent.part3.part3body.timesheet2.total.weekTotal,2)

var W3Hour = Left(form1.flow.page.parent.part4.part4body.timesheet3.total.weekTotal,2)

var W4Hour = Left(form1.flow.page.parent.p5.part5body.timesheet3.total.weekTotal,2)

var TotalWHour = sum(W1Hour, W2Hour, W3Hour, W4Hour)

var Minutes = Mod(TotalWMin,60)

var FinTot  = floor((TotalWMin / 60 )) + (TotalWHour)

var MinRem

if (Minutes < 10) then

MinRem = concat("0", Minutes)

else

MinRem = Minutes

endif

\$.rawValue = concat(FinTot, ":", MinRem)

Calculating the Total Number of Conditioned Hours

var c1Min = Right(form1.flow.page.parent.part2.part2body.std.stdHours, 2)

var c2Min = Right(form1.flow.page.parent.part3.part3body.std2.stdHours, 2)

var c3Min = Right(form1.flow.page.parent.part4.part4body.std2.stdHours, 2)

var c4Min = Right(form1.flow.page.parent.p5.part5body.std2.stdHours,2)

var TotalCMin = sum(c1Min, c2Min, c3Min, c4Min)

var c1Hour = Left(form1.flow.page.parent.part2.part2body.std.stdHours,2)

var c2Hour = Left(form1.flow.page.parent.part3.part3body.std2.stdHours,2)

var c3Hour = Left(form1.flow.page.parent.part4.part4body.std2.stdHours,2)

var c4Hour = Left(form1.flow.page.parent.p5.part5body.std2.stdHours,2)

var TotalCHour = sum(c1Hour, c2Hour, c3Hour, c4Hour)

var Minutes = Mod(TotalCMin,60)

var FinTot  = floor((TotalCMin / 60 )) + (TotalCHour)

var MinRem

if (Minutes < 10) then

MinRem = concat("0", Minutes)

else

MinRem = Minutes

endif

\$.rawValue = concat(FinTot, ":", MinRem)

Calculating the Difference Between Total Hours and Conditioned Hours

//Establish variables for calculations

var minGain     = Right(form1.flow.page.parent.part2.part2body.hoursGain,2) // Gets the last two digits of the Hours Gained field to establish the number of minutes

var weekMin     = Right(form1.flow.page.parent.p6.part6.totalHours, 2)      // Gets the last two digits of the Total Hours field to establish the number of minutes

var totalMin    = sum(minGain,weekMin)                                      // Add the minutes together

var hrGain      = Left(form1.flow.page.parent.part2.part2body.hoursGain,2) // Gets the first two digits of the Hours Gained field to establish the number of hours

var weekHr      = Left(form1.flow.page.parent.p6.part6.totalHours,2)        // Gets the first two digits of the Total Hours field to establish the number of hours

var totalHours  = sum(hrGain,weekHr)                                        // Adds the number of hours together

var Minutes     = Mod(totalMin,60)

var finalTotal  = floor((totalMin / 60 )) + (totalHours)

var minRem

if      (Minutes < 10 )

then    minRem = concat("0",Minutes)

else    minRem = Minutes

endif

//\$.rawValue = concat(finalTotal,":",minRem)

var     totHrs          =   Left(form1.flow.page.parent.p6.part6.totalHours,3)

var     totMins         =   Right(form1.flow.page.parent.p6.part6.totalHours,2)

var     condHrs         =   Left(form1.flow.page.parent.p6.part6.textField,3)

var     condMins        =   Right(form1.flow.page.parent.p6.part6.textField,2)

var     gainLossHrs     =   (totHrs - condHrs)

var     gainLossMins    =   (totMins - condMins)

var     gainLossRem

if      (gainLossMins < 10)

then    gainLossRem = concat("0",gainLossMins)

else    gainLossRem = gainLossMins

endif

\$.rawValue  = concat(gainLossHrs,":",gainLossRem)

6 Replies

Level 10

Hi,

just a different approach by myself.

To calculate the time difference between two time I use the following script.

It recognises if the date line has been passed (worked from 10.00pm to 07.00 am for example) so you cannot get negative results.

var TimeCount

var OneMinute = 1/60

if (StartTime ne null and EndTime ne null) then

//if start time is lower than end time.

if (Time2Num(StartTime.formattedValue, "HH:MM") < Time2Num(EndTime.formattedValue, "HH:MM")) then

TimeCount = Abs(Time2Num(StartTime.formattedValue, "HH:MM") - Time2Num(EndTime.formattedValue, "HH:MM")) /(60 * 60 * 1000)

//If start time is higher than end time we passed the date line

else

TimeCount = 24 - Abs(Time2Num(EndTime.formattedValue, "HH:MM") - Time2Num(StartTime.formattedValue, "HH:MM")) /(60 * 60 * 1000)

endif

endif

\$ = Round(TimeCount / OneMinute)

To get the number of hours and minutes in the format HH:MM I add a for loop.

var TimeCount

var OneMinute = 1/60

if (StartTime ne null and EndTime ne null) then

//if start time is lower than end time.

if (Time2Num(StartTime.formattedValue, "HH:MM") < Time2Num(EndTime.formattedValue, "HH:MM")) then

TimeCount = Abs(Time2Num(StartTime.formattedValue, "HH:MM") - Time2Num(EndTime.formattedValue, "HH:MM")) /(60 * 60 * 1000)

//If start time is higher than end time

else

TimeCount = 24 - Abs(Time2Num(EndTime.formattedValue, "HH:MM") - Time2Num(StartTime.formattedValue, "HH:MM")) /(60 * 60 * 1000)

endif

var Minutes = Round(TimeCount / OneMinute)

var Hours = 0

for i = 0 upto 48 step 1 do

if (Minutes >= 60) then

Minutes = Minutes - 60

Hours = i + 1

endif

endfor

\$ = Concat(Hours, " hours and ", Minutes, " minutes")

endif

For the total minutes I simply use

Sum(Page.Day[*].ResultMinutes)

And for the total hours and minutes

var TimeCountTotal = Sum(Page.Day[*].ResultMinutes)

var Hours = 0

for i = 0 upto 144 step 1 do

if (TimeCountTotal >= 60) then

TimeCountTotal = TimeCountTotal - 60

Hours = i + 1

endif

endfor

\$ = Concat(Hours, " hours and ", TimeCountTotal, " minutes")

Here's a sample form I made for you, hope this helps

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

Level 2

That sample form is unreachable now. I am having similar issues. Can you repost it somewhere please sir?

Level 10

Hi,

the sample is still there, only the link has changed in one single character.

https://acrobat.com/?d=hFcOm4rNgQBO6DzYzrrKwA

Level 2

Former Community Member

I am trying to make what I thought was going to be a simple task timing form but I cant seem to get the formats or total calulation to work.

I created a simple table that adds a row when the user clicks a button. I used your sample script for the total field, but I want to make it easier for the user for the start/end times.

I added the function \$=Num2Time(Time()) to the click event of the start and stop cells. This works ok in adding in a time to each cell when the user clicks but it wont display the seconds no matter what I try.

The issue is I cant seem to get the start/stop cells to show HH:MM:SS and the total cell always shows 1440.

If I go back to manually entering numbers, the total works fine.

Any suggestions on how to correct my click event so it works with the total script?

Level 1

sorry to bother can you please provide the link again, because this wont work
Thanks