If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem is sum hours, because when cross 24h start in zero
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 |
#2
|
|||
|
|||
Problem is sum hours, because when cross 24h start in zero
"Marco" wrote in message
... 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, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
Problem is sum hours, because when cross 24h start in zero
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 "Rick Brandt" wrote: "Marco" wrote in message ... 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, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#4
|
|||
|
|||
Problem is sum hours, because when cross 24h start in zero
"Marco" wrote in message
... 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, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#5
|
|||
|
|||
Problem is sum hours, because when cross 24h start in zero
Yes. the comma the decimal point character in my regional settings.
Can you help me? Marco "Rick Brandt" wrote: "Marco" wrote in message ... 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, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#6
|
|||
|
|||
Problem is sum hours, because when cross 24h start in zero
"Marco" wrote in message
... 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? -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#7
|
|||
|
|||
Problem is sum hours, because when cross 24h start in zero
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 "Rick Brandt" wrote: "Marco" wrote in message ... 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? -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#8
|
|||
|
|||
Problem is sum hours, because when cross 24h start in zero
On Thu, 23 Nov 2006 08:19:01 -0800, Marco
wrote: 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] |
#9
|
|||
|
|||
Problem is sum hours, because when cross 24h start in zero
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 "John Vinson" wrote: On Thu, 23 Nov 2006 08:19:01 -0800, Marco wrote: 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] |
#10
|
|||
|
|||
Problem is sum hours, because when cross 24h start in zero
John,
I've got this number and I want that appears 149. Regards, Marco "John Vinson" wrote: On Thu, 23 Nov 2006 08:19:01 -0800, Marco wrote: 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] |
|
Thread Tools | |
Display Modes | |
|
|