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

Time Calculation

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.
So it is possible? Is there a place were I can begin to figure out this calculation?
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
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.
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.
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);
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 😞
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?
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.
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.