Trying to total minutes and seconds stored in text field

[Craig]

I am using an old Access 97 database which is used to key in timesheet
information. The application was desigend to interface with a
customized payroll application which will only accept a CSV file with
each field containing text.

I have created a summary report which lists out each timesheet keyed
in for the week and a manager is now requesting that total hours be
summarized to reconcile the weekly edit list. I managed to create a
new text field at the bottom of the report which uses
"=SUM(VAL(RegHours))" to calculate the total number of hours for the
week. It totals the numbers okay but is obviously adding this up as a
decimal field and not minutes:time. For that reason I get totals like
790 instead of 8:30.

Can anyone think of an easy way to have my calculations totaled as
time and not decimal values? I have tried various things but can't
seem to find a solution which will work in this instance.

Assistance appreciated...

 

[Jeff Boyce]

Craig

Define "easy"...<G>

If this were mine, I'd add up the minutes, then "calculate" an hh:mm display
by dividing by 60 to get the hh-part and taking the remainder (see the MOD()
function) for the mm-part.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Craig]

Craig

Define "easy"...<G>

If this were mine, I'd add up the minutes, then "calculate" an hh:mm display
by dividing by 60 to get the hh-part and taking the remainder (see the MOD()
function) for the mm-part.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -

The problem I have is that I have a string of text, such as 0345,
which represents 3 hours and 45 minutes. The string isn't all minutes
so I can't divide by 60 to get the hours...in the case above 3 is the
hours.

Example:

0345 = 3h45m
+0330 = 3h30m
+0245 = 2h45m
===========
TOTAL: 0920 (this is what I get on my report). The total should be
10h00min.

??

 

[Jeff Boyce]

Craig

So, you're adding up 'text' and expecting it to work like a 'number'?

If "0345" means 3h 45m, you'll have to tell Access that.

First, it sounds like you'll need to parse what you have into an hours part
and a minutes part, then add up the hours parts and the minutes parts, then
use the 'divide by 60' trick on the sum of minutes to find how many
additional hours you need to add, then use the MOD() function on the total
minutes part to find how many minutes you have.

Wouldn't it be easier to store a number like 225 (3 hours, 45 minutes) and
add those?<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

Craig

Define "easy"...<G>

If this were mine, I'd add up the minutes, then "calculate" an hh:mm
display
by dividing by 60 to get the hh-part and taking the remainder (see the
MOD()
function) for the mm-part.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -

The problem I have is that I have a string of text, such as 0345,
which represents 3 hours and 45 minutes. The string isn't all minutes
so I can't divide by 60 to get the hours...in the case above 3 is the
hours.

Example:

0345 = 3h45m
+0330 = 3h30m
+0245 = 2h45m
===========
TOTAL: 0920 (this is what I get on my report). The total should be
10h00min.

??