add short time formatted cells to get total time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to add cells with time values to get a total amount of time.Short time
format only counts up to 24 hrs and then starts from 0. I want a total time
of say, 400hrs 15mins
 
If you are adding "cells" then I would assume you are using Excel (since
Access does not have sells). If that is the case, please post to an Excel
newsgroup.

If you are using Access, then the item you describe is not a "TIME" it is a
"DURATION". Time is a given set point in time. Duration is the number of
hours, minutes, or seconds that elapsed during a particular event.

Please give us an example of your data and what it represents. Also tell us
what the result would be and how you figured it out.
 
Don't bother doing this in Date/Time data fields; it won't work. The easy way
to get your result is to use the CSng() function on your time fields, as in:

Dim TotTime As Single, TotHrs As Long, TotMins As Long

TotTime = 0

After opening your recordset (I'm assuming you're doing this in VBA, but it
will work in a query as well), make a loop, to include the following line of
code:

TotTime = TotTime + CSng(YourTimeField)

At the end of the loop (meaning after EOF), your TotTime will be a mishmash
of numbers, maybe 5.678 or some incomprehensible number.

Now take the number and process as follows:

TotHrs = Int(TotTime * 24)
TotMins = Round(((TotTime * 24) - TotHrs) * 60) 'to round to the nearest
minute

You can then display this any way you want.

Hope this helps,

Sam

p.s. The reason this works is because while dates and times display in the
accustomed manner, they are actually stored as numbers: the number to the
left of the decimal are days, the number to the right of the decimal are
fractions of a day. Therefore, 5.678 represents 5.678 days. All you'll be
doing is separating this into usable hours and minutes.
 
Back
Top