Timesheet form – problem summing minutes

[331]

Hi All,
I've spent the better part of 2 weeks developing a Timesheet form for employees and students to enter their own hours worked. The code has the Timesheet calculate time worked each day in Hours:Minutes.

PROBLEM: I need to sum this for each pay period, and it is not summing correctly. Unless I’m really tired and looking at this wrong, the problem appears to be in adding portions of an hour. Can someone help me to get a correct sum of hours worked without re-working my entire code - it's rather cumbersome. I’ve read most of the 42 questions for help in the Form section pertaining to Timesheets, seeing if I could figure out my problem!

ATTACHED: Small example of incorrect summing. Screenshot of Timesheet. Code to calculate time worked each day in Hours:Minutes

Any suggestions are truly welcome! If I get this summing correctly, I can send the Timesheet database to post as a sample.

 

[331]

After further thought -- and re-reading previous answers/suggestions to timesheet questions in the Forums . . .

How do I convert a number (double) field that contains the hrs.min,
Example: convert 2.45 (having worked from 09:00 am to 11:45 am) to 2.75

I need to convert the 45 minutes to a percentage of an hour (.75) and then add it on to the hr. I've isolated the minutes using =Right([fieldName],2)/60 which works fine for values > .00 but gives incorrect value for all others.

I've also tried this which is not accurate.
=Left([HrsWorked1a ],1)+Right([HrsWorked1a],2)/60*100

Any suggestions for this formatting conversion? Then I can properly sum the hrs and fractions of hrs.

Or . . . should I back up, revise my table/form and not use this medium time format with am/pm? This timesheet will be in use for several years and I'd better get it right. I appreciate all suggestions. Thanks!!

 

[331]

RESOLVED - I've found a way to change minutes to a percentage of an hour, in order to calculate total hours worked.

Field is named [HrsWorked] which provides values in hours/minutes such as 2.15, 2.30, or 2.45 etc. To convert the minutes to percentages of an hour (and get 2.25, 2.50, 2.75 for the previous values) use the mod mathematical operator. In Microsoft Access, mod is different when decimals are involved, compared to the mod (modulus) that is used to do math calculation, but this works fine in MS Access:

In the query, create new field [HrsWorkedNew] to perform math calc on [yourFieldName]
HrsWorkedNew = IIf(([HrsWorked] Mod 1000)>[HrsWorked],[HrsWorked]+(2/3)*([HrsWorked]+1-([HrsWorked] Mod 1000)),[HrsWorked]+(2/3)*([HrsWorked]-([HrsWorked] Mod 1000)))


If the great majority of cases, you can shorten this code to:
HrsWorkedNew = ([HrsWorked]+(2/3)*([HrsWorked]-([HrsWorked] Mod 1000)))