Problem is sum hours, because when cross 24h start in zero

[Marco]

Hello. How can I sum a total of hours. I'm having this problem. When the
total of hours cross 24h it restart, like it was passing to another day.
Exemple: 18.00H+10.00h appears as 04.00h. and I wnat to appear 26.00h.

Con should I format this? In excel I format as [h] and it works, but it's
not working here.

Regards,
Marco

 

[Rick Brandt]

Hello. How can I sum a total of hours. I'm having this problem. When the
total of hours cross 24h it restart, like it was passing to another day.
Exemple: 18.00H+10.00h appears as 04.00h. and I wnat to appear 26.00h.

Con should I format this? In excel I format as [h] and it works, but it's
not working here.

Databases do not have a data type for storing *amounts of time*. The DateTime
field is intended for "points in time". In a database 18:00 means 6 PM, not 18
hours and zero minutes.

Since many database systems store DateTimes under the covers as numeric values
you can sometimes get way with using these fields as if they were durations, but
as you have seen that blows up when you cross 24 hour boundaries.

The recommended approach is to store your durations as regular numbers like the
number of minutes or the number of seconds using an Integer or Long Integer
field. Then you can do all kinds of math on them easily. Final results can for
display purposes be converted back to an hours and minutes format.

 

[Marco]

Hi. thanks for your reply. I already have the column hours in long integer.
I have this total sum: 45,75399583.

If it was in excel, I format in costum as [h] it will work fine. I wanted
to do the same in access, in querie or form. in excel I can get 1098 but not
in access.

Regards,
Marco

Hello. How can I sum a total of hours. I'm having this problem. When the
total of hours cross 24h it restart, like it was passing to another day.
Exemple: 18.00H+10.00h appears as 04.00h. and I wnat to appear 26.00h.

Con should I format this? In excel I format as [h] and it works, but it's
not working here.

Databases do not have a data type for storing *amounts of time*. The DateTime
field is intended for "points in time". In a database 18:00 means 6 PM, not 18
hours and zero minutes.

Since many database systems store DateTimes under the covers as numeric values
you can sometimes get way with using these fields as if they were durations, but
as you have seen that blows up when you cross 24 hour boundaries.

The recommended approach is to store your durations as regular numbers like the
number of minutes or the number of seconds using an Integer or Long Integer
field. Then you can do all kinds of math on them easily. Final results can for
display purposes be converted back to an hours and minutes format.

 

[Rick Brandt]

Hi. thanks for your reply. I already have the column hours in long integer.
I have this total sum: 45,75399583.

If it was in excel, I format in costum as [h] it will work fine. I wanted
to do the same in access, in querie or form. in excel I can get 1098 but not
in access.

I fail to see how you can go from a sum of hours equaling 45,75399583 hours to
the number 1098.

Is the comma the decimal point character in your regional settings?

 

[Marco]

Yes. the comma the decimal point character in my regional settings.

Can you help me?

Marco

Hi. thanks for your reply. I already have the column hours in long integer.
I have this total sum: 45,75399583.

If it was in excel, I format in costum as [h] it will work fine. I wanted
to do the same in access, in querie or form. in excel I can get 1098 but not
in access.

I fail to see how you can go from a sum of hours equaling 45,75399583 hours to
the number 1098.

Is the comma the decimal point character in your regional settings?

 

[Rick Brandt]

Yes. the comma the decimal point character in my regional settings.

Can you help me?

So you have a total sum of hours with a value of 45,75399583, which I'm
interpreting to be a bit over 45 and 3/4 hours. Just how do you want that
displayed?

 

[Marco]

Try to past this number into a excel sheet. Then format this cell, goto
Format cells an in custom use this kind of format [h] it will convert the
numbers into hours.

Marco

 

[John Vinson]

Hi. thanks for your reply. I already have the column hours in long integer.
I have this total sum: 45,75399583.

If it was in excel, I format in costum as [h] it will work fine. I wanted
to do the same in access, in querie or form. in excel I can get 1098 but not
in access.

Try

Fix([Total]) & ":" & Format(60*[Total] - Fix([Total]), "00")


John W. Vinson[MVP]

 

[Marco]

Hi. Well with that code you I’m approaching of what I need. But instead of
counting 83h counts 6:367h.

Any idea?

Regards,
Marco

Hi. thanks for your reply. I already have the column hours in long integer.
I have this total sum: 45,75399583.

If it was in excel, I format in costum as [h] it will work fine. I wanted
to do the same in access, in querie or form. in excel I can get 1098 but not
in access.

Try

Fix([Total]) & ":" & Format(60*[Total] - Fix([Total]), "00")


John W. Vinson[MVP]

 

[Marco]

John,

I've got this number and I want that appears 149.

Regards,
Marco

Hi. thanks for your reply. I already have the column hours in long integer.
I have this total sum: 45,75399583.

If it was in excel, I format in costum as [h] it will work fine. I wanted
to do the same in access, in querie or form. in excel I can get 1098 but not
in access.

Try

Fix([Total]) & ":" & Format(60*[Total] - Fix([Total]), "00")


John W. Vinson[MVP]

 

[Rick Brandt]

Hi. Well with that code you I'm approaching of what I need. But instead of
counting 83h counts 6:367h.

Any idea?

I still don't understand. You indicated that the number was a sum of hours and
as a general number you have 45 plus some fractional amount. To me that means
you have a total of 45 hours plus a fraction of an hour. How do you expect to
get 83 from this number?

 

[Marco]

Hi Rick,

Sorry, maybe I said something wrong this 6,21264767199444 is 149 hours.

Put this number in a excel cell and then format with this code [h], as you
know, goto custon cell format.

Thanks for your pacience.
Marco

 

[Marco]

Rick, can I send you an e-mail?

Hi Rick,

Sorry, maybe I said something wrong this 6,21264767199444 is 149 hours.

Put this number in a excel cell and then format with this code [h], as you
know, goto custon cell format.

Thanks for your pacience.
Marco

And how does one get from 45.75399583 hours to the number 149?

 

[Rick Brandt]

Hi Rick,

Sorry, maybe I said something wrong this 6,21264767199444 is 149 hours.

Put this number in a excel cell and then format with this code [h], as you
know, goto custon cell format.

Thanks for your pacience.

Well then what you have then is a sum of DAYS equaling 6.21...., not a sum of
hours as you stated before.

This expression Fix(YourValue*24) will give you the number of whole hours
(149).

 

[Marco]

Thanks, it worked very fine.

How did you get there?


Marco

Hi Rick,

Sorry, maybe I said something wrong this 6,21264767199444 is 149 hours.

Put this number in a excel cell and then format with this code [h], as you
know, goto custon cell format.

Thanks for your pacience.

Well then what you have then is a sum of DAYS equaling 6.21...., not a sum of
hours as you stated before.

This expression Fix(YourValue*24) will give you the number of whole hours
(149).

 

[Rick Brandt]

Thanks, it worked very fine.

How did you get there?

Once I determined that you had a value representing a number of days instead
of a number of hours the math was pretty easy to figure out.