Calculating hours when greater than 24

[Andy]

Hi,

I may be in the wrong group for this but wondered if
anybody could help....

I am doing a Time Sheet Database, and so far I have a
function that will display hours and minutes and also an
unbound field that will give an hourly total for the week.

I have a field in a table for "contracted hours" BUT if i
set this to the date/time format, i am unable to enter
more than 23 hours (when set to "h:nn")

The aim here is to provide a weekly "flexi" total, thus
if someone is contracted to 37 hours a week, and my form
has already calculated that they have worked 32 hours,
the flexi should be -5:00.

If i set the field to a number that would just store 37
then how would i use this in a calculation that also uses
my hours and minutes function (i.e convert 37 to 37:00:00)

I hope I have explained clearly

TIA

Andy

 

[Roger Carlson]

On my website (see sig below) is a small sample database called
"ConvertStringStuff2k.mdb" which illustrates one way to do this. See the
"Add String Times" form.

 

[Andy]

Thanks Roger.

I will give that a try

Andy

-----Original Message-----
On my website (see sig below) is a small sample database called
"ConvertStringStuff2k.mdb" which illustrates one way to do this. See the
"Add String Times" form.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org




.

 

[Marshall Barton]

I am doing a Time Sheet Database, and so far I have a
function that will display hours and minutes and also an
unbound field that will give an hourly total for the week.

I have a field in a table for "contracted hours" BUT if i
set this to the date/time format, i am unable to enter
more than 23 hours (when set to "h:nn")

The aim here is to provide a weekly "flexi" total, thus
if someone is contracted to 37 hours a week, and my form
has already calculated that they have worked 32 hours,
the flexi should be -5:00.

If i set the field to a number that would just store 37
then how would i use this in a calculation that also uses
my hours and minutes function (i.e convert 37 to 37:00:00)

The Date/Time field type is for points in time, not for
durations.

You could use a number (double) to store the number of hours
and fraction of an hour or two integers, one for hours and
the other for minutes.

Personally, I would probabluy use a Long to store the total
number of minutes and do the required calculations to
convert it as needed. For example, if the user enters 37
hours in the contracted text box, just multiple by 60 to
store it as 2220 minutes, which can be then be displayed as
37:00 using this expression:
=Format(minutes\60, "0" & ":" & Format(Minutes Mod 60, "00")

 

[Andy]

Hi Marshall,

Thanks for that...

I have tried this and it does convert to minutes.

However, I then have to do the same with my other field
now that I had got into a text string with hours and
minutes as I need to calculate the difference between
hours worked and contracted hours....

That isn't too much of a problem, as I have done that...

ok, I now have two more fields...one which has the
contracted hours value as minutes, and one that has the
total hours worked in week as minutes.

I also have my difference field which is the difference
between the 2 above, also showing the same in minutes.

The problem starts here when I am trying to convert this
minutes back into hours and minutes text string. I have
tried many different alterations, but problems occur when
there are no hours e.g 55 minutes and again when there is
a minus e.g -55.

Do you know the easiest way to get this difference field
back to an hour:minute format e.g -55 would be -0:55, -
125 would be -2:05, 5 would be 0:05 and 75 would be 1:15
etc etc. Like I say i have compensated for anomalies in
my code, but this will then throw out another problem
when testing other minus and plus times. I need the field
to show a minus sign (if person has worked less than
contracted)e.g "-0:55" but doesn't have to show a plus
(if worked more)e.g "0:55"

I hope you understand what I mean. I can send you my
current code if that is any help (although that will
probably make things worse...LOL)

TIA

Andy

 

[Marshall Barton]

Hi Marshall,

Thanks for that...

I have tried this and it does convert to minutes.

However, I then have to do the same with my other field
now that I had got into a text string with hours and
minutes as I need to calculate the difference between
hours worked and contracted hours....

That isn't too much of a problem, as I have done that...

ok, I now have two more fields...one which has the
contracted hours value as minutes, and one that has the
total hours worked in week as minutes.

I also have my difference field which is the difference
between the 2 above, also showing the same in minutes.

The problem starts here when I am trying to convert this
minutes back into hours and minutes text string. I have
tried many different alterations, but problems occur when
there are no hours e.g 55 minutes and again when there is
a minus e.g -55.

Do you know the easiest way to get this difference field
back to an hour:minute format e.g -55 would be -0:55, -
125 would be -2:05, 5 would be 0:05 and 75 would be 1:15
etc etc. Like I say i have compensated for anomalies in
my code, but this will then throw out another problem
when testing other minus and plus times. I need the field
to show a minus sign (if person has worked less than
contracted)e.g "-0:55" but doesn't have to show a plus
(if worked more)e.g "0:55"

The expression I posted before only deals with a positive
number of minutes. You can use this one to get the
negatives too:

Format(Sgn(Minutes),";-") & Format(Abs(Minutes\60), "0") &
":" & Format(Abs(Minutes Mod 60), "00")

 

[Andy]

Hi Marsh,

Thanks for your help. That did the trick! Much
appreciated.

Andy

-----Original Message-----

Hi Marshall,

Thanks for that...

I have tried this and it does convert to minutes.

However, I then have to do the same with my other field
now that I had got into a text string with hours and
minutes as I need to calculate the difference between
hours worked and contracted hours....

That isn't too much of a problem, as I have done that...

ok, I now have two more fields...one which has the
contracted hours value as minutes, and one that has the
total hours worked in week as minutes.

I also have my difference field which is the difference
between the 2 above, also showing the same in minutes.

The problem starts here when I am trying to convert this
minutes back into hours and minutes text string. I have
tried many different alterations, but problems occur when
there are no hours e.g 55 minutes and again when there is
a minus e.g -55.

Do you know the easiest way to get this difference field
back to an hour:minute format e.g -55 would be -0:55, -
125 would be -2:05, 5 would be 0:05 and 75 would be 1:15
etc etc. Like I say i have compensated for anomalies in
my code, but this will then throw out another problem
when testing other minus and plus times. I need the field
to show a minus sign (if person has worked less than
contracted)e.g "-0:55" but doesn't have to show a plus
(if worked more)e.g "0:55"

The expression I posted before only deals with a positive
number of minutes. You can use this one to get the
negatives too:

Format(Sgn(Minutes),";-") & Format(Abs(Minutes\60), "0") &
":" & Format(Abs(Minutes Mod 60), "00")
--
Marsh
MVP [MS Access]

Andy if
i enters
37 displayed
as

.