Problem w/Date time function

G

Guest

Hi

I’m having a problem with the below function which I got from Microsoft’s
web site. It’s working just fine as long as I have a start date/time and a
end date/time the problem is that some of my cases are still open when a
report is run so there is no end date/time in which case I get an #Error. I
would like to use Now() as the end date/time if the end date/time is null so
I can see the elapsed time up to the time the report is run. I have tried
putting it into the ElapsedTimeDouble function with no success. Any help
with this will be greatly appreciated

Thanks
Chuck





Option Compare Database

Option Explicit


Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As
Date) As String
'*********************************************************************
' Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As
String
' Returns the time elapsed between a starting Date/Time and an ending
' Date/Time formatted as a string that looks like this:
' "10 days, 20 hours, 30 minutes, 40 seconds".
'*********************************************************************

ElapsedTimeString = _
ElapsedTimeStringFormat( _
ElapsedTimeDouble(dateTimeStart, dateTimeEnd))

End Function

Public Function ElapsedTimeDouble(dateTimeStart As Date, _
dateTimeEnd As Date) As Double
If IsNull(dateTimeStart) = True Or IsNull(dateTimeEnd) = True Then
Exit Function
End If

ElapsedTimeDouble = dateTimeEnd - dateTimeStart

End Function

Public Function ElapsedTimeStringFormat(interval As Double) As String
Dim str As String, days As Variant
Dim hours As String, minutes As String, seconds As String
days = Fix(CSng(interval))
hours = Format(interval, "h")
minutes = Format(interval, "n")
seconds = Format(interval, "s")

' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(hours & minutes & seconds <> "000", ", ", " "))
' Hours part of the string
str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(minutes & seconds <> "00", ", ", " "))
' Minutes part of the string
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", minutes & " Minutes"))
str = str & IIf(minutes = "0", "", IIf(seconds <> "0", ", ", " "))
' Seconds part of the string
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", seconds & " Seconds"))
ElapsedTimeStringFormat = IIf(str = "", "0", str)

End Function





Public Function HoursAndMinutes(interval As Variant) As String
'***********************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'***********************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))

totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60

totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

If seconds > 30 Then minutes = minutes + 1 ' round up the minutes and
If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust hours

HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function



Public Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As Date) As
String
'*********************************************************************
' Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As Date) As String
' Returns the time elapsed in days between a starting Date/Time and
' an ending Date/Time formatted as a string that looks like this:
' "10 days" or "1 day".
'*********************************************************************
Dim interval As Double, days As Variant
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function
interval = dateTimeEnd - dateTimeStart
days = Fix(CSng(interval))
ElapsedDays = IIf(days = 1, days & " Day", days & " Days")
End Function
 
B

Bob Quintal

Hi

I’m having a problem with the below function which I got from
Microsoft’s web site. It’s working just fine as long as I have
a start date/time and a end date/time the problem is that some of
my cases are still open when a report is run so there is no end
date/time in which case I get an #Error. I would like to use Now()
as the end date/time if the end date/time is null so I can see the
elapsed time up to the time the report is run. I have tried
putting it into the ElapsedTimeDouble function with no success.
Any help with this will be greatly appreciated

Thanks
Chuck
Click to expand...
The function is fine, you just need to slightly modify the statement
that calls it to check for null and stuff the now(). The nz()
function will do that..

Something = ElapsedTimeString([date1], nz([date2],now()))



Q
Option Compare Database

Option Explicit


Public Function ElapsedTimeString(dateTimeStart As Date,
dateTimeEnd As Date) As String
'***************************************************************** *
*** ' Function ElapsedTimeString(dateTimeStart As Date,
dateTimeEnd As Date) As String
' Returns the time elapsed between a starting Date/Time and an
ending ' Date/Time formatted as a string that looks like this:
' "10 days, 20 hours, 30 minutes, 40 seconds".
'***************************************************************** *
***

ElapsedTimeString = _
ElapsedTimeStringFormat( _
ElapsedTimeDouble(dateTimeStart, dateTimeEnd))

End Function

Public Function ElapsedTimeDouble(dateTimeStart As Date, _
dateTimeEnd As Date) As Double
If IsNull(dateTimeStart) = True Or IsNull(dateTimeEnd) = True
Then
Exit Function
End If

ElapsedTimeDouble = dateTimeEnd - dateTimeStart

End Function

Public Function ElapsedTimeStringFormat(interval As Double) As
String Dim str As String, days As Variant
Dim hours As String, minutes As String, seconds As String
days = Fix(CSng(interval))
hours = Format(interval, "h")
minutes = Format(interval, "n")
seconds = Format(interval, "s")

' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(hours & minutes & seconds <> "000", ", ", " "))
' Hours part of the string
str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(minutes & seconds <> "00", ", ", " "))
' Minutes part of the string
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", minutes & "
Minutes"))
str = str & IIf(minutes = "0", "", IIf(seconds <> "0", ", ",
" ")) ' Seconds part of the string
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", seconds & "
Seconds"))
ElapsedTimeStringFormat = IIf(str = "", "0", str)

End Function





Public Function HoursAndMinutes(interval As Variant) As String
'***************************************************************** *
***** ' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'***************************************************************** *
***** Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))

totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60
mins minutes = totalminutes Mod 60

totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60
secs seconds = totalseconds Mod 60

If seconds > 30 Then minutes = minutes + 1 ' round up the minutes
and If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust
hours

HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function



Public Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As
Date) As String
'***************************************************************** *
*** ' Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As
Date) As String ' Returns the time elapsed in days between a
starting Date/Time and ' an ending Date/Time formatted as a string
that looks like this: ' "10 days" or "1 day".
'***************************************************************** *
*** Dim interval As Double, days As Variant
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function
interval = dateTimeEnd - dateTimeStart
days = Fix(CSng(interval))
ElapsedDays = IIf(days = 1, days & " Day", days & " Days")
End Function
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating Time Difference 2
elapsed time calculation help needed 1
Rewrite a Module 2
Date, Time Calculations 1
Adding timer to text box 1
Unresolved Issue about totaling Downtime Hours 1
How to calculate time using this function 10
Re-Writing Module 3

Top