Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

Time Calculation

Avatar

Former Community Member
I am trying to to create a employee time sheet. In day one box an employee puts 1 hour and 30 minutes of work then in day two box they enter in 1 hour and 30 minutes of work. In the total box I need to add up those two boxes to get the total of 3 hours, what is the calculation to do this?
18 Replies

Avatar

Level 7
Well this would be alot of work. You have a custom format that would have to split into hours and minutes, convert the hours to minutes, then get the hours and minutes out of the total, and format the output string.

Avatar

Former Community Member
So it is possible? Is there a place were I can begin to figure out this calculation?

Avatar

Former Community Member
What if I changed the numeric values to round .59 to 1.00?

Avatar

Former Community Member
Lisa,



Make each box a decimal field where the user types in their time in hours, that is, 1.5 like you mention would be 1 hour and 30 minutes.



Then the formula would simply convert to minutes and then back to hours. So the formula for your total box would be like the following:



( (day1*60) + (day2*60) + (day3*60) + (day4*60) + (day5*60) ) / 60

Avatar

Former Community Member
I tried your formula and when I entered 1.33 for 1 hour and 33 minutes of work in day one and then in day two, the total is still 2.66 instead of 3.06. It still doesn't round up. I need to tell it that after .59 (59minutes) then .60 is really 1.00 (one hour).

Avatar

Level 7
Lisa, the hour is divided into 1/100 so each minute is 0.01666 and 1 hour 33 minutes would be 1.55.



If you want to enter the time as 1:33 you will need to learn how to split the text string into hours and minutes and then do math with 1 hour equal to 60 minutes, convert the resulting minutes back into hours and minutes and build the text display string.



You will have to use JavaScript to manipulate the string with either the "split()" method or the RegExp object.

Avatar

Former Community Member
Thanks for your help, I understand that an hour is divided in 1/100 but i'm trying to make it easier for my staff. I don't know how to use javascript I'm lucky I figured out how to total up the rest of the form this is the last thing I need to work on.

Avatar

Level 7
Well, expensive expense/time systems like Expide use 1/100 hour increments for recording time. You can have your users record time in quarter hours.

Avatar

Former Community Member
Lisa,



I think I've got a formula for you. Sorry about the first formula not working at all. Below is what I did in Excel so you can try it also. Using your original example, do the following:



1. Enter 1.33 in cell A1

2. Enter into A2: =INT(A1)*60 + MOD(A1,INT(A1)) * 100

3. Enter into A3: =INT(A2/60)&":"&MOD(A2,60)



What this shows you is the followwing:

A1 = Time in hours in minutes. In our example, it's 1.33

A2 = Time from A1 in only minutes. In our example, its 93 minutes.

A3 = Time from A2 converted back to hours and minutes.



So if you want to add multiple 1.33's, then simply apply the step 2 formula on each 1.33 value, then sum them all up. Then you apply the step 3 formula to the sum.

Avatar

Level 7
The following JavaScript function can compute the minutes form the entry of time in the format of "H:MM".



// document level function for reuse in many fields

function PFN_SplitHours(cTime) {

/*

split passed time string in the format of "h:mm" into hours and minutes

and return number of minutes for the entered time

*/

// split time string into an array at the ";"

var aTime = cTime.split(":");

var fMin; // time in minutes;

if (aTime.length != 2)

fMin = 0; // invalid time string, return 0 minutes

else

fMin = Number(aTime[0]) * 60 + Number(aTime[1]) // compute time in minutes

// return minutes

return fMin;

} // end PFN_SplitHours



You can then sum the all the cells and format the output using the JavaScirpt "util.print()" method for the hours and minutes



$.formattedValue = util.printf("%,0 01.0f", sHours) + ":" + util.printf("%,302.0f", sMin);

Avatar

Former Community Member
I appreciate everyone's help, I've tried all the different formulas. Geo, I am very new at this and don't understand javascript. Scott I entered in your formula and it keeps telling me that it doesn't understand what INT means. I take it out of the formula and still get a mess :(

Avatar

Former Community Member
I thank everyone for their help, I have finally been able to get the caluclations to work. I appreciate the formulas and ideas. My last newbie question is how can I get the form to save?

Avatar

Former Community Member
You should post the code in case someone could use it.

Avatar

Former Community Member
I basically just had the staff add everything in minutes, summed it all up then divided it by 60 so that it does convert it to 1/100 of a minute.

Avatar

Level 2
Lisa, if you have ever used Excel formulas you can use a formula such as SUM("Name of Cell"+"Name of Cell"). You put this formula into cell that contains your total. Make sure to look at the top left hand side of the page to make sure that the drop down box is calculate. Once you have put the formula in, press the "+" sign.



This should work for you.

Avatar

Former Community Member
If any one has figured out how to carry out this calculation. i.e. 1:30 + 1:40 = 3:10 rather that 2:70. Please post here.



Thanks

Avatar

Level 7
You will have to use the "AT()" function to locate the ":" and split the string into the hours component and then the minutes component. You can then compute the string into minutes. With each string converted to minutes, you can add the minutes getting the total minutes. Now you can easily do the math to get the hours and minutes from the total minutes.