# MS Access 2003: Duration

Good Morning,

I am working on a duration field that calculates the elapsed time based on the start and end times displaying the results as “Days Hrs Mins”.  I have made it so that if the duration is less than 24 hours then the “Days” are not displayed.  However, I am having trouble replicating this for the “Hrs” as well.  If the duration is less than 60 minutes I would like “Hrs” not to be displayed.  Also, if the minutes equal zero then I would like minutes not to be displayed.  And lastly, if the duration results in only 1 minute or 1 hour I would like to leave the “s” off of “Hrs” and Mins”.

Here is the code thus far.

IIF(DateDiff("n",[Start],[End]) < 1440, "", DateDiff("n",[Start],[End])\1440 & " Days ")
& (DateDiff("n",[Start],[End])\60) Mod 24 & " Hrs "
& Format(DateDiff("n",[Start],[End]) Mod 60,"00") & " Mins"

I know this seems a bit convoluted, but I would greatly appreciate any help.

Thank you
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:

Public Sub fnDuration(StartAt as variant, EndAt as Variant) as string

Dim intDays as integer
Dim intHours as integer
Dim intMinutes as integer

If isnull(StartAt) or isnull(EndAt) Then
fnDuration = "invalid entry"
else
intMinutes = DateDiff("n", StartAt, EndAt)
intDays = intMinutes\1440
intMinutes = intMinutes - intDays * 1440
intHours = intMinutes\60
intMinutes = intMinutes - intHours * 60

fnDuration = iif(intDays=0, NULL, intDays & " Days ") _
& iif(intHours = 0, "", intHours & " Hrs ") _
& iif(intMinutes = 0, "", intMinutes & " Mins:
End If

End Sub
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
You will want to check out a "More Complete DateDiff Function" ...

http://www.accessmvp.com/djsteele/Diff2Dates.html

Examples:
? Diff2Dates("dhns", #08/01/2010 00:00:00#, #08/01/2010 00:00:02#)
2 seconds

? Diff2Dates("dhns", #08/01/2010 00:00:00#, #08/01/2010 00:00:01#)
1 second

? Diff2Dates("dhns", #08/01/2010 00:00:00#, #08/01/2010 00:01:01#)
1 minute 1 second

? Diff2Dates("dhns", #08/01/2010 00:00:00#, #08/01/2010 00:02:01#)
2 minutes 1 second

? Diff2Dates("dhns", #07/31/2010 00:00:00#, #08/01/2010 00:02:01#)
1 day 2 minutes 1 second
0
Author Commented:
Fyed,
Thank you for the reply.  I was able to modify your function a bit to make it work; however, I am having a little trouble identifying when eirther the hours or minutes equal 1.  When this happens I would like to have it to drop the "s" and display only "Hr" or "Min".

Here's what I have so far:

Public Sub fnDuration(StartAt as variant, EndAt as Variant) as string

Dim intDays as integer
Dim intHours as integer
Dim intMinutes as integer

If isnull(StartAt) or isnull(EndAt) Then
fnDuration = "invalid entry"
else
intMinutes = Format(DateDiff("n",[Start],[End]) Mod 60,"00")
intDays = DateDiff("n",[Start],[End])\1440
intHours = DateDiff("n",[Start],[End])\60) Mod 24

fnDuration = iif(intDays=0, NULL, intDays & " Days ") _
& iif(intHours = 0, "", intHours & " Hrs ") _
& iif(intMinutes = 0, "", intMinutes & " Mins)
End If

End Sub

Thanks
0
Commented:
The code I linked you to would be a direct copy and paste and provide the functionality you desire (dropping the "s" when appropriate, and dropping the unit when it is 0), you should give it a shot to see if it meets your needs.  You may have to modify the literal strings for the units, but that would be it.
0
Commented:
Try:

fnDuration = iif(intDays=0, NULL, intDays & " Days ") _
& iif(intHours = 0, "", intHours & " Hr" & iif(intHours = 1, " ", "s ")) _
& iif(intMinutes = 0, "", intMinutes & " Min" & iif(intMinutes = 1, "", "s "))
0
Author Commented:
Fyed,

That did it.  Thanks a lot.  I appreciate the help.