Full Version: Time keeping
UtterAccess Forums > Microsoft® Access > Access Forms
ruskitank
Hello, I am new to this forum...
My main question is about how to add or subtract time in Microsoft Access. I am building a basic program where I input the hours & minutes I have and how much are spent. Then automaticaly in the third box it should appear the difference between these two. I got through the format procedure where I choose that this is time/date I am inputing but the main problem is that it only goes up to 24 hours!!!, and I might need to put more than that! I tried setting it just as a number (seperated by a colon) but then then numbers go above 60 minutes....XD
Can anyone tell me how to make my time (hours) go above 24 hours???
Thx, for any ideas!
R. Hicks
Welcome to Utter Access Forums ... frown.gif
Search Access Help files for the DateDiff() function to return the elapsed time between two Date/Time values ..
RDH
ruskitank
Is there anyway, to just minus for example 64:00 from 80:00?
R. Hicks
Again .. the DateDiff() function is the "correct" way to calculate the difference between two Date/Time values ...

Also ... 64:00 and 80:00 are not valid Time values ...

RDH
ruskitank
Is there a known way to remove the limit of 24 hours? anywhere in options? Because I might need to also do a sum of hours making my total higher than 24
R. Hicks
You don;t seem to understand what I have been saying ....
If you use the DateDiff() function to calculate the elapsed time .. there is no 24 hour limt.
You simply need the date and time values to get the result ...
RDH
truittb
The hours & minutes you input, are they the actual clock times, or are they the elapsed times between two actual times?
R. Hicks
How many clocks have you seen that output values such as "64:00" and "80:00" ???
The problem .. more than likely is that the values are a Text datatype and should be a Date/Time datatype.
Then the eplased time can very easily be calculated ...
RDH
truittb
I was thinking more of numeric values where he was entering elasped time and then trying to use datetime functions on the sum of these values. Which won't work.
ScottGem
Rusk,
You need to understand how time is stored in Access. A Date/Time field is stored as number. The Integer part of that number represnts the number of days since 12/30/1899. The Decimal part of that number represents a fraction of a day. So .25 = 6AM or 6 hours. When you do time arithmetic, the difference is stored correctly as x days and the remainder as a fraction of a day. But when you FORMAT a control to display time format, it ignores the Integer portion . That's where the 24 hour limit comes in. Not in Access ability to calculate the time difference but in its ability to display time.
o you have 2 options, you can either display the time difference in minutes using DateDiff or you can fudge the display but calculating the minutes, dividing by 60 to get the hours and then subtracting to get theremainder in mintues and then using string conversion to display.
For example, lets say you 1938 minutes. That translates to 32 hours and 18 minutes. So you could calculate it out and then display it as hrs & ":" & min
HTH
R. Hicks
Exactly ....

The output of the DateDiff() needs to be in the lowest common denominator .. which in this case is minutes.
So .. you use the DateDiff() to output the total elapsed minutes betweeen the two time vaules and then you can use math to break this elapsed minutes value into the final output and format desired ...

If the ending time value spans past midnight into another day .. then the Date value must be included with the Time value to correctly calculate the elapsed time ...

RDH
ruskitank
Thx, for the help, well u see I am not really using actual time I am justusing this form to calculate the total amount of hours spent on a project, for example: one day 15hours 30 minutes, then 20 hours, then 15 more hours, in total u will see that there will be more than 24 hours, I need to see the toal amount of hours so I can see how much I need to pay the person for their work....so I might try to do this arithmetically, thx again for the help!
JoeCosta
Maybe all you need is a report to SUM your values.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.