Formatting time sum field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul - 15 Jan 2008 22:35 GMT

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.

Signature

Paul

Reply to this Message

Jeff Boyce - 15 Jan 2008 23:32 GMT

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
[quoted text clipped - 3 lines]
> separator isn't critical. Just want the results all shown in one field.)
> Any ideas? Thanks in advance.

Reply to this Message

Paul - 16 Jan 2008 05:00 GMT

Thanks Jeff. Sorry for delayed response.

Signature

Paul

> Paul
>
[quoted text clipped - 18 lines]
> > separator isn't critical. Just want the results all shown in one field.)
> > Any ideas? Thanks in advance.

Reply to this Message

Bob Quintal - 15 Jan 2008 23:38 GMT

> I want to calculate the difference between two date fields and
> display as total days:hours:minutes. Is this possible? I've used
[quoted text clipped - 3 lines]
> 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

Signature

Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Reply to this Message

Bob Quintal - 15 Jan 2008 23:40 GMT

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

Signature

Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Reply to this Message

Paul - 17 Jan 2008 20:08 GMT

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

Signature

Paul

> > I want to calculate the difference between two date fields and
> > display as total days:hours:minutes. Is this possible? I've used
[quoted text clipped - 18 lines]
> & Format(hh, "00") & ":" & Format(mm, "00")
> End Function

Reply to this Message