How to sum date/time field

[jg_1981]

Hello everyone!

I have a problem to solve: how to sum a date/time field inm access?

I have a table with multiple fields, one of them it's the "Hour" field. When I apply the total option, I want to sum the total of hours in that fiel.

I want to obtain the 09:00 result.

But the accesss gives me the result below

How can I turn this around?

Thank you all for your help.

Best regards

JG

 

[Galaxiom]

You don't. DateTime fields are for recording moments in time, not amounts of time.

Record the number of seconds, minutes or hours is integers and sum them.

 

[Ranman256]

calculate the elapsed time in integer minutes (not time format). Then just sum the minutes.

 

[jg_1981]

You don't. DateTime fields are for recording moments in time, not amounts of time.

Record the number of seconds, minutes or hours is integers and sum them.

Thanks for ypur reply! I'll try that! Not what I want but thanks anyways!

 

[jg_1981]

calculate the elapsed time in integer minutes (not time format). Then just sum the minutes.

Thanks for your answer! I'll try that.

 

[The_Doc_Man]

The real headache would occur when your total of several elapsed times exceeded one day, because that is almost impossible for novices to format correctly without a little help. But your expected total of 9:00 should have worked if you did this carefully.

Despite the suggestions to convert the times so that you can count minutes (which are actually not wrong), what you showed us in post #1 SHOULD have produced a total other than 0:00 because of the way date/time fields are stored. Which means something else is going on that is (to us) as yet unseen.

How did you compute the sum? If it was SQL, show us the SQL statement. If you created a query using the query design grid, open that query in SQL view, which is text you can cut/paste. If it wasn't SQL, then what did you do?

The behavior you showed would be correct if somehow an integer got involved, because DATE fields keep time as fractions of a day. If this supposed integer was somehow involved, it would strip the fractions. The other possibility is that those times are actually strings despite their appearance, and SUM doesn't work too well with string data types. At least, not as you might expect.

 

[GPGeorge]

Hello everyone!

I have a problem to solve: how to sum a date/time field inm access?

I have a table with multiple fields, one of them it's the "Hour" field. When I apply the total option, I want to sum the total of hours in that fiel.

I want to obtain the 09:00 result.

View attachment 108591

But the accesss gives me the result below

View attachment 108592

How can I turn this around?

Thank you all for your help.

Best regards

JG

The secret is that your screenshots do not display times; they display elapsed periods of time. I.D., I.E., 1:30 would normally be the point in time either one hour and thirty minutes after midnight or one hour and thirty minutes after noon. The AM/PM is missing, so that's another level of ambiguity.

Instead, what you have displayed is supposed to represent an elapsed time. It represents a duration of 90 minutes. It is formatted to resemble a time.

That can take a bit of thought, sometimes, in order to understand why they are different.

The problem comes about because we can use our experience and human knowledge to interpret the representation differently when we see what we assume to be a point in time or a duration of time (the absence of an AM/PM is a partial clue). To Access, that kind of subtlety is a serious problem.

The solution is not to format anything for display until you get to the very end of the process when you are ready to show the user "1:30" as a specially formatted representation of "one and a half hours" or "9:00" as a specially formatted representation of "nine hours".
Others have offered ways to calculate the values you need.

 

[The_Doc_Man]

To further clarify: IF (and only if) your data storage is to a DATE field but you NEVER involve an actual date, then for the first 23 hours, 59 minutes, and 59 seconds, you CAN add times in DATE fields because the times will represent times on day 0 of the Access calendar. The normal formatting routines will work for that long. The MOMENT you reach or exceed one day, you are done with that approach, because the Windows and Access date formatting routines do not support a format such as hhh:nn:ss - hours (greater than a day), minutes, and seconds. They only go up to 23:59:59 and then flip over to the start of the next day.

@GPGeorge, your explanation is absolutely correct, but in the case presented, up to one day of accumulated time, the two concepts APPEAR the same and even WORK the same. Which is why I originally chimed in that something else was going on. Even though all of the other replies that say "don't do it this way" are correct.