Formatting time sum field

[Paul]

I want to calculate the difference between two date fields and display as
total days:hours:minutes. Is this possible? I've used
DateDiff('n',[StartDate],[EndDate]) to calculate the difference as minutes,
but don't know how to format the result as days:hours:minutes. (The ":"
separator isn't critical. Just want the results all shown in one field.)
Any ideas? Thanks in advance.

 

[Jeff Boyce]

Paul

If you try to use Access date/time formatting, as soon as you total more
than 24 hours it appears to "reset".

You'll need to create (or locate) a procedure that parses total minutes into
a dd:hh:mm-appearing format.

Try checking at mvps.org/access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bob Quintal]

I want to calculate the difference between two date fields and
display as total days:hours:minutes. Is this possible? I've used
DateDiff('n',[StartDate],[EndDate]) to calculate the difference as
minutes, but don't know how to format the result as
days:hours:minutes. (The ":" separator isn't critical. Just want
the results all shown in one field.) Any ideas? Thanks in
advance.

one way is to write a simple function to take the minutes and format
the parts

public function Duration(byval iMinutes as long) as string
dim iHours as integer
iHours = iMinutes \ 60
iMinutes = iMinutes mod 60

Duration = format(iHours, "###0") & ":" & format(iMinutes, "00")

end function

 

[Bob Quintal]

I want to calculate the difference between two date fields and
display as total days:hours:minutes. Is this possible? I've used
DateDiff('n',[StartDate],[EndDate]) to calculate the difference as
minutes, but don't know how to format the result as
days:hours:minutes. (The ":" separator isn't critical. Just want
the results all shown in one field.) Any ideas? Thanks in
advance.

this does days too

Public Function Minutes_2_Duration(minutes As Long) As String
Dim dd As Integer
Dim hh As Integer
Dim mm As Integer

dd = minutes \ 1440
minutes = minutes - dd * (24 * 60)
hh = minutes \ 60
mm = minutes Mod 60
Minutes_2_Duration = Format(dd, "000") & ":" _
& Format(hh, "00") & ":" & Format(mm, "00")
End Function

 

[Paul]

Thanks Jeff. Sorry for delayed response.
--
Paul

Paul

If you try to use Access date/time formatting, as soon as you total more
than 24 hours it appears to "reset".

You'll need to create (or locate) a procedure that parses total minutes into
a dd:hh:mm-appearing format.

Try checking at mvps.org/access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I want to calculate the difference between two date fields and display as
total days:hours:minutes. Is this possible? I've used
DateDiff('n',[StartDate],[EndDate]) to calculate the difference as
minutes,
but don't know how to format the result as days:hours:minutes. (The ":"
separator isn't critical. Just want the results all shown in one field.)
Any ideas? Thanks in advance.

 

[Paul]

Thanks Bob, for both of your replies. I'll give them a try.
--
Paul

I want to calculate the difference between two date fields and
display as total days:hours:minutes. Is this possible? I've used
DateDiff('n',[StartDate],[EndDate]) to calculate the difference as
minutes, but don't know how to format the result as
days:hours:minutes. (The ":" separator isn't critical. Just want
the results all shown in one field.) Any ideas? Thanks in
advance.

this does days too

Public Function Minutes_2_Duration(minutes As Long) As String
Dim dd As Integer
Dim hh As Integer
Dim mm As Integer

dd = minutes \ 1440
minutes = minutes - dd * (24 * 60)
hh = minutes \ 60
mm = minutes Mod 60
Minutes_2_Duration = Format(dd, "000") & ":" _
& Format(hh, "00") & ":" & Format(mm, "00")
End Function