How do I convert ???

[zrazzaq]

Hi I have a major issue:
I have a report which spits out daily, MTD, yearly CMS numbers.
Now the problem is the daily comes out perfect because I download the csv file as is.
Adding up time for MTD, Yearly becomes hard.
Now, the daily report field is like 6:57:13 which is 6hours 57 minutes 13 seconds.
For example if we had 6:00:00 (6hours) and another day was (9:15:57) (9hours 15 minutes and 57 seconds)
The users want to see these two added as:
15:15:57 (15 hours 15minutes and 57 seconds)

Any suggestions in access. I can convert it into minutes and add it up then divide by 60 but how do i get it in the format they would like. IM LOST.
Thanks
Zishan

 

[BSman]

One question you haven't answered is how do they expect times over 24 hours to be shown? In other words, as an example, is 49 hours and 30 minutes shown as 2 days, 1 hour, and 30 minutes, or just 49:30:00?

Bob

 

[MajP]

This might be overkill. But my assumption is that you can have MTD values greater than 24 hours. I do not kown what the abbreviation stands for, but if that is not the case then this could be much simpler using datetime functions.

Public Function addMTD(mtd1 As String, mtd2 As String) As String
  Dim intHours As Integer
  Dim intMinutes As Integer
  Dim intseconds As Integer
  Dim multMinutes As Integer
  Dim multSeconds As Integer
  intHours = MTDHours(mtd1) + MTDHours(mtd2)
  intMinutes = MTDMinutes(mtd1) + MTDMinutes(mtd2)
  intseconds = MTDSeconds(mtd1) + MTDSeconds(mtd2)
  If intseconds > 59 Then
    intMinutes = intMinutes + intseconds \ 60
    intseconds = intseconds - (intseconds \ 60) * 60
  End If
  If intMinutes > 59 Then
    intHours = intHours + intMinutes \ 60
    intMinutes = intMinutes - (intMinutes \ 60) * 60
  End If
  addMTD = intHours & ":" & Format(intMinutes, "00") & ":" & Format(intseconds, "00")
  End Function

Public Function MTDHours(mtd As String) As Integer
  MTDHours = CInt(Left(mtd, InStr(mtd, ":") - 1))
End Function

Public Function MTDMinutes(mtd As String) As Integer
  MTDMinutes = CInt(Mid(mtd, InStr(mtd, ":") + 1, 2))
End Function

Public Function MTDSeconds(mtd As String) As Integer
  MTDSeconds = CInt(Right(mtd, 2))
End Function

Public Sub testMTD()
  MsgBox addMTD("123:50:01", "2:11:20")
End Sub

The sub functions might be helpful for other calculation.

 

[MajP]

Now if you can only have MTD values less than 24 hours (which I think is the case)
Simply pass in a legitimate time value to this simpler function.

Public Sub testMTD()
  simplerAddMTD(#22:50:01#, #2:11:20#)
End Sub

Public Function simplerAddMTD(mtd1 As Date, mtd2 As Date) As String
  Dim dateAdd As Date
  Dim daysAhead As Integer
  dateAdd = mtd1 + mtd2
  daysAhead = (Day(dateAdd) - Day(mtd1))
  simplerAddMTD = (daysAhead * 24) + Hour(dateAdd) & ":" & Format(Minute(dateAdd), "00") & ":" & Format(Second(dateAdd), "00")
End Function

I assume that cummulative hours greater than 24 hours are just displayed as
125:50:50

 

[zrazzaq]

Thanks again...The last one seem to work nice until it gets to PM and AM
For example:
simplerAddMTD(7:56:30 AM, 9:36:41 PM)
Any suggestions...

 

[zrazzaq]

I take that back...it works...sorry but thanks

 

[zrazzaq]

Ok One more thing with the last function:
When it gets to 29:11:13 which I love that is what the users want to see, i get the data mismatch
I think I know why but do not know how to correct it
I start out by doing

Dim h As Date
b = #12:00:00 AM#
Do until rs.eof
       if   
       b= simplerAddMTD(rs("TTL_TIME_STAFFED"), b)
 
rs.movenext
loop

Now when b gets to 29 hours it bums out. I change b to string then got a Data mismatch error.'
Anything????
Thanks
Zishan

 

[zrazzaq]

Dim h is actually dim b as date made a mistake last message...

 

[MajP]

Yeah,
Can you get the first one to work? If you can then this should work


b = "12:00:00"
Do until rs.eof
if
b= AddMTD(Format(rs("TTL_TIME_STAFFED"),"hh:nn:ss"), b)
rs.movenext
loop

this way you pass in a string and return a string. The problem with the way you did it is that you will eventually pass in a number like 29:00:05 as a date which is not a valid date.