How to calculate time using this function

J

Jen

The following is a module I use to calculate time. I use the following
string in my reports. But now I need to know how to Add all the calculated
time to show manpower per report not just a record.

String:
=IIf([EndDate]>0,ElapsedTimeString([StartDate],[EndDate]),0)

Module:
Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As
Date) As Strin
'**********************************************************************************************
' 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"
'**********************************************************************************************

Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String

If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

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"))
ElapsedTimeString = IIf(str = "", "0", str)

End Function
 
J

Jeff Boyce

Jen

If your function returns the value(s) you need, call it from a query by
adding it as a new field in the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

Jen

Okay, Jeff I did that already but it only shows the time for the first record
and I might have a total of 1000 records and it shows 1 minutes.




Jeff Boyce said:
Jen

If your function returns the value(s) you need, call it from a query by
adding it as a new field in the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jen said:
The following is a module I use to calculate time. I use the following
string in my reports. But now I need to know how to Add all the calculated
time to show manpower per report not just a record.

String:
=IIf([EndDate]>0,ElapsedTimeString([StartDate],[EndDate]),0)

Module:
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".
'**********************************************************************************************

Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String

If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

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"))
ElapsedTimeString = IIf(str = "", "0", str)

End Function
Click to expand...
Click to expand...
 
J

Jeff Boyce

Jen

I'm not there, so I can't see how you are doing that...

If I were facing your (described) situation, I'd:

1) create the function that returns a number of minutes, given a
specific recordID
2) create a query that returns all recordIDs in which I am interested
3) add a call to the function to get the number of minutes for each
recordID (I'd pass the recordID into the function call in the query)
4) if I needed a grand total, I'd use the Totals toolbar button.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jen said:
Okay, Jeff I did that already but it only shows the time for the first
record
and I might have a total of 1000 records and it shows 1 minutes.




Jeff Boyce said:
Jen

If your function returns the value(s) you need, call it from a query by
adding it as a new field in the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jen said:
The following is a module I use to calculate time. I use the following
string in my reports. But now I need to know how to Add all the
calculated
time to show manpower per report not just a record.

String:
=IIf([EndDate]>0,ElapsedTimeString([StartDate],[EndDate]),0)

Module:
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".
'**********************************************************************************************

Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String

If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

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"))
ElapsedTimeString = IIf(str = "", "0", str)

End Function
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Try an expression in your report footer text box control source like:

=ElapsedTimeString(0, Sum( Nz([EndDate], [StartDate]) - [StartDate]))

Not sure if I got this correct especially since we don't know if any values
might be null.
 
J

Jen

Okay Duane you are my hero. Now I am not going to lie but someone else wrote
the code for me. Kinda easy to tell I know. What I need to know is how to
have it calculate in hours and minutes only. Could or Would you help me?

Duane Hookom said:
Try an expression in your report footer text box control source like:

=ElapsedTimeString(0, Sum( Nz([EndDate], [StartDate]) - [StartDate]))

Not sure if I got this correct especially since we don't know if any values
might be null.


--
Duane Hookom
MS Access MVP

Jen said:
The following is a module I use to calculate time. I use the following
string in my reports. But now I need to know how to Add all the calculated
time to show manpower per report not just a record.

String:
=IIf([EndDate]>0,ElapsedTimeString([StartDate],[EndDate]),0)

Module:
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".
'**********************************************************************************************

Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String

If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

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"))
ElapsedTimeString = IIf(str = "", "0", str)

End Function
Click to expand...
Click to expand...
 
D

Duane Hookom

I wouldn't re-write or modify the function when you can use Doug Steele's
function at http://www.accessmvp.com/djsteele/Diff2Dates.html.
Use the same basic expression:

Diff2Dates("hn", 0, #01/26/2002 20:10:34#,Sum( Nz([EndDate], [StartDate]) -
[StartDate] ) )

--
Duane Hookom
MS Access MVP

Jen said:
Okay Duane you are my hero. Now I am not going to lie but someone else
wrote
the code for me. Kinda easy to tell I know. What I need to know is how
to
have it calculate in hours and minutes only. Could or Would you help me?

Duane Hookom said:
Try an expression in your report footer text box control source like:

=ElapsedTimeString(0, Sum( Nz([EndDate], [StartDate]) - [StartDate]))

Not sure if I got this correct especially since we don't know if any
values
might be null.


--
Duane Hookom
MS Access MVP

Jen said:
The following is a module I use to calculate time. I use the following
string in my reports. But now I need to know how to Add all the
calculated
time to show manpower per report not just a record.

String:
=IIf([EndDate]>0,ElapsedTimeString([StartDate],[EndDate]),0)

Module:
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".
'**********************************************************************************************

Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String

If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

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"))
ElapsedTimeString = IIf(str = "", "0", str)

End Function
Click to expand...
Click to expand...
Click to expand...
 
J

Jen

Okay Duane when I enter the equation below it gives me an invalid Syntax. I
am trying to modify the Query and then write the report.

Duane Hookom said:
I wouldn't re-write or modify the function when you can use Doug Steele's
function at http://www.accessmvp.com/djsteele/Diff2Dates.html.
Use the same basic expression:

Diff2Dates("hn", 0, #01/26/2002 20:10:34#,Sum( Nz([EndDate], [StartDate]) -
[StartDate] ) )

--
Duane Hookom
MS Access MVP

Jen said:
Okay Duane you are my hero. Now I am not going to lie but someone else
wrote
the code for me. Kinda easy to tell I know. What I need to know is how
to
have it calculate in hours and minutes only. Could or Would you help me?

Duane Hookom said:
Try an expression in your report footer text box control source like:

=ElapsedTimeString(0, Sum( Nz([EndDate], [StartDate]) - [StartDate]))

Not sure if I got this correct especially since we don't know if any
values
might be null.


--
Duane Hookom
MS Access MVP

The following is a module I use to calculate time. I use the following
string in my reports. But now I need to know how to Add all the
calculated
time to show manpower per report not just a record.

String:
=IIf([EndDate]>0,ElapsedTimeString([StartDate],[EndDate]),0)

Module:
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".
'**********************************************************************************************

Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String

If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

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"))
ElapsedTimeString = IIf(str = "", "0", str)

End Function
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

My bad. I was copying and pasting syntax from Doug's web page. You need to
get rid of one of the arguments sent to the function.

I think you should be able to figure out what should be removed. If you
can't, come on back and ask again.


--
Duane Hookom
MS Access MVP

Jen said:
Okay Duane when I enter the equation below it gives me an invalid Syntax.
I
am trying to modify the Query and then write the report.

Duane Hookom said:
I wouldn't re-write or modify the function when you can use Doug Steele's
function at http://www.accessmvp.com/djsteele/Diff2Dates.html.
Use the same basic expression:

Diff2Dates("hn", 0, #01/26/2002 20:10:34#,Sum( Nz([EndDate],
[StartDate]) -
[StartDate] ) )

--
Duane Hookom
MS Access MVP

Jen said:
Okay Duane you are my hero. Now I am not going to lie but someone else
wrote
the code for me. Kinda easy to tell I know. What I need to know is
how
to
have it calculate in hours and minutes only. Could or Would you help
me?

:

Try an expression in your report footer text box control source like:

=ElapsedTimeString(0, Sum( Nz([EndDate], [StartDate]) - [StartDate]))

Not sure if I got this correct especially since we don't know if any
values
might be null.


--
Duane Hookom
MS Access MVP

The following is a module I use to calculate time. I use the
following
string in my reports. But now I need to know how to Add all the
calculated
time to show manpower per report not just a record.

String:
=IIf([EndDate]>0,ElapsedTimeString([StartDate],[EndDate]),0)

Module:
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".
'**********************************************************************************************

Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String

If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

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"))
ElapsedTimeString = IIf(str = "", "0", str)

End Function
Click to expand...
Click to expand...
Click to expand...
 
J

Jen

That is what I am begging for. I have spent two hours this morning trying to
figure it out. I am at the point I give up, please, please, please. I used:

Diff2Dates("hn", 0, #01/26/2002 20:10:34#,Sum( Nz([EndDate],[StartDate])
-[StartDate] ) )

Is it?

Diff2Dates("hn", 0, Sum( Nz([EndDate],[StartDate]) -[StartDate] ) )

at your mercy
Jen
Duane Hookom said:
My bad. I was copying and pasting syntax from Doug's web page. You need to
get rid of one of the arguments sent to the function.

I think you should be able to figure out what should be removed. If you
can't, come on back and ask again.


--
Duane Hookom
MS Access MVP

Jen said:
Okay Duane when I enter the equation below it gives me an invalid Syntax.
I
am trying to modify the Query and then write the report.

Duane Hookom said:
I wouldn't re-write or modify the function when you can use Doug Steele's
function at http://www.accessmvp.com/djsteele/Diff2Dates.html.
Use the same basic expression:

Diff2Dates("hn", 0, #01/26/2002 20:10:34#,Sum( Nz([EndDate],
[StartDate]) -
[StartDate] ) )

--
Duane Hookom
MS Access MVP

Okay Duane you are my hero. Now I am not going to lie but someone else
wrote
the code for me. Kinda easy to tell I know. What I need to know is
how
to
have it calculate in hours and minutes only. Could or Would you help
me?

:

Try an expression in your report footer text box control source like:

=ElapsedTimeString(0, Sum( Nz([EndDate], [StartDate]) - [StartDate]))

Not sure if I got this correct especially since we don't know if any
values
might be null.


--
Duane Hookom
MS Access MVP

The following is a module I use to calculate time. I use the
following
string in my reports. But now I need to know how to Add all the
calculated
time to show manpower per report not just a record.

String:
=IIf([EndDate]>0,ElapsedTimeString([StartDate],[EndDate]),0)

Module:
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".
'**********************************************************************************************

Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String

If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

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"))
ElapsedTimeString = IIf(str = "", "0", str)

End Function
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Rather than asking "Is it?" just try it. It looks like it should work to me.


--
Duane Hookom
MS Access MVP

Jen said:
That is what I am begging for. I have spent two hours this morning trying
to
figure it out. I am at the point I give up, please, please, please. I
used:

Diff2Dates("hn", 0, #01/26/2002 20:10:34#,Sum( Nz([EndDate],[StartDate])
-[StartDate] ) )

Is it?

Diff2Dates("hn", 0, Sum( Nz([EndDate],[StartDate]) -[StartDate] ) )

at your mercy
Jen
Duane Hookom said:
My bad. I was copying and pasting syntax from Doug's web page. You need
to
get rid of one of the arguments sent to the function.

I think you should be able to figure out what should be removed. If you
can't, come on back and ask again.


--
Duane Hookom
MS Access MVP

Jen said:
Okay Duane when I enter the equation below it gives me an invalid
Syntax.
I
am trying to modify the Query and then write the report.

:

I wouldn't re-write or modify the function when you can use Doug
Steele's
function at http://www.accessmvp.com/djsteele/Diff2Dates.html.
Use the same basic expression:

Diff2Dates("hn", 0, #01/26/2002 20:10:34#,Sum( Nz([EndDate],
[StartDate]) -
[StartDate] ) )

--
Duane Hookom
MS Access MVP

Okay Duane you are my hero. Now I am not going to lie but someone
else
wrote
the code for me. Kinda easy to tell I know. What I need to know is
how
to
have it calculate in hours and minutes only. Could or Would you
help
me?

:

Try an expression in your report footer text box control source
like:

=ElapsedTimeString(0, Sum( Nz([EndDate], [StartDate]) -
[StartDate]))

Not sure if I got this correct especially since we don't know if
any
values
might be null.


--
Duane Hookom
MS Access MVP

The following is a module I use to calculate time. I use the
following
string in my reports. But now I need to know how to Add all the
calculated
time to show manpower per report not just a record.

String:
=IIf([EndDate]>0,ElapsedTimeString([StartDate],[EndDate]),0)

Module:
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".
'**********************************************************************************************

Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String

If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

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"))
ElapsedTimeString = IIf(str = "", "0", str)

End Function
Click to expand...
Click to expand...
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

Re-Writing Module 3
Calculating Time Difference 2
Countdown macro - display settings 2
elapsed time calculation help needed 1
Problem w/Date time function 2
working hours counter in Access 0
Rewrite a Module 2
Unresolved Issue about totaling Downtime Hours 1

Top