Adding Time greater than 24 hours

[AccessAlan]

Hi Folks.

I'm losing my mind on a database I've put together. I need to track the men in the workshop, what they are doing and how long they are doing it.
I have a report for summarising the time they spend on each task but it wont add duration's greater than 24 hours. It basically resets itself back to 24 hours and doesn't add the time correctly. I know this must be a simple solution but I'm poor at SQL and have wing-ed my way this far.
I've created a report called 'all projects on a page'

Could anyone help please. I'm at a standstill. I have attached a link to the database if anyone can assist...

https://www.dropbox.com/s/0jsr6ltitf...ues.accdb?dl=0

Many thanks in advance.



Al

[ArviLaanemets]

Format cell(s) you want hours over 24 h displayed like "[h]:mm:ss" or "[h]:mm"

[ArviLaanemets]

Format cell(s) you want hours over 24 h dsplyed like "[h]:mm:ss" or "[h]:mm"

Edit: Scrap this. I's Excel feature.

You can calculate summary time (TimeTotal) in datetime format. the result will be like 03.01.1900 04:20:00 (i.e. 3 days 4 hours and 20 minutes. Now you can calculate total hours (TimeHours) as

=INT(TimeTotal*24)

which returns 76.
Remaining minutes (TimeMinutes) you can calculate as

INT(TimeTotal*24 - INT(TimeTotal*24))*60)

which returns 20.

[CJ_London]

sorry - only download from this forum - so cannot look at your db.

From what you are saying it sounds like you do not quite understand what a date field actually is

Date/Time is stored as a number, what you see as a date is just a format.

The number is a decimal number with the value before the dp representing the date (number of days since 31/12/1899) and the bit after the time represented as number of seconds divided by 86400 (the number of seconds in a day) e.g.

?now()
11/10/2019 10:43:56
?cdbl(now())
43749.4471875

so when you are adding time, you are adding the .4471875

ergo if it goes over 24 hours, it will become 1.something - and you are looking at the something and not taking into account the 1. As you have discovered, the time format only show time based on 24 hours

to solve this you need a function that will account for the 1 - probably the datediff function. If you google your question I'm sure you will find some example code out there as it is a common requirement, but if you want to learn how to do it yourself, this is the principle you need to follow

lets say the sum of the difference between start time and end time is 1.65 days - at the moment you are seeing the .65 (15 hours)

so multiply difference by the number of seconds in a day
1.65*86400= 142560

now you need to convert that to hours - so divide by 3600 (number of seconds in an hour) using the \ to return a whole number
142560\(60*60)= 39
now divide the remainder by 60 (number of seconds in a minute), again using a \
2160\60=36
and the final remainder is the number of seconds - in this example 0

So you now have 3 values - number of hours, minutes and seconds

You can now concatenate those into a string to display on your form.

[AccessAlan]

Hi folks, thanks for the response. Hopefully i've uploaded the db and you can see what i mean...
al_REV 5 - Time Adding Issues.zip

[CJ_London]

do take a look at the suggestions already provided to see if they match your requirement. If they don't, please explain why not

Also your db has many reports - please tell us which one you are talking about and which control/code whatever is not working as expected

[AccessAlan]

Hi Ajax

I dont believe the reply's answer my specific question. I understand the concept of time as a decimal but if you look at my report qry Operations on a page, youll see that the time field is not adding its just reseting when it goes over 24 hours.

I really hope this makes sense. Not sure how else to explain it without you guys seeing the actual database and how im using it.

Al

[CJ_London]

I understand perfectly and the situation is as I described. If you format the values as decimals, this is what you see

the .604 converted as I demonstrated gives you 14:30


You will also notice in the first picture that the Fab column includes a date element (1) so either that process took 25.5 hours or you have an issue there as well

To reiterate, the time format can only format to 24 hours, if you want to see number of hours/minutes, you need to calculate it

[AccessAlan]

Ajax

Thank you so much. That works perfect for me. You have no idea how grateful I am. Understanding how access formats the date field ie 1 before the decimal is key.

Many thanks again, very much appreciated.

Al