24+ Hour Time

[steve1111]

Hello,

I have a SQL table that stores time as Time(0). I have a report that list all the employees hours for each day during the week as HH:MM. i need to find a way to total that time for the week so that for example 8:00+8:00+9:15 = 25:15.

I have tried to add a field in the report and take the TimeValue([Hours]) to get a double so that i can add the time up to 25.25. My issues comes when trying to convert that back to HH:MM so the end user doesn't see decimal time.

I am currently using:

int([hours]) & ":" & int(([hours]-int([hours]))*60)

Which works well except when i have a decimal like 10.03 which should convert to 10:02 but is coming out 10:2.

I am sure i am making this more complicated than it needs to be. My follow up question is should i have stored these hours as time in SQL or something else?

Thanks

 

[Minty]

Something like
int([hours]) & ":" & Format(int(([hours]-int([hours]))*60),"00")

 

[steve1111]

Ok thanks Minty, I am getting 10:01 now but i expected the 10.03 decimal to go to 10:02, i am sure this is a rounding issue, but since i want to stay consistent with the payroll software, is there a way to address that rounding issue?

 

[gemma-the-husky]

just add the times, and treat the result as double.

so 25 hours 15 minutes is approx. 1 1/16 day

to convert a decimal time to a "date/time", you need to be divide by 60 (hours) and then 24 (days)

see examples here

Sub handletime()
Dim t As Date
dim s as string

'display the numerical value of a datetime
    t = TimeSerial(25, 15, 0)
    MsgBox CDbl(t)
    
    s = Int(t * 24) & ":" &   Round(((t * 24) - Int(t * 24)) * 60, 2) 

'convert decimal minutes to days
    t = 10.03 / 60 / 24
    MsgBox t
End Sub

 

[gemma-the-husky]

I see now

to convert a "date/time" of 1.083333 days, back to hours and minutes:

you can't do this by normal means


you have to multiply this by 24 to get hours (the integer part), and then multiply the remainder by 60 to get the minutes part, which has already been suggested, I think.
It's a bit tricky getting this bit correct.

when I check the above, my 0.25 hour originally evaluated as .2499999999, which is why I added the rounding function. I imagine the difference you noticed in Minty's calculation was caused by a similar issue.

 

[steve1111]

While not elegant piecing multiple post together this worked:

CStr(Int(Sum(CDbl([txtDecimal])*24))) & ":" & Format(Right([txtSumDecimal],2)*0.6,"00")


Thanks everyone

 

[gemma-the-husky]

steve

is that just a typo - you have txtdecimal in one term, and txtsumdecimal in the other.

 

[steve1111]

No they are two different hidden fields. the txtDecimal is the TimeValue for each individual day for each driver. the tstDecimalSum is the Sum of all the txtDecimal fields. not elegant but i am still learning and a moderate Access user so i tend to break things apart to work through what is causing me the issues, i am sure i could clean it up and combine some of it.