"If possible I would also like to exclude the hours between midnight
and 5
AM."
That sort of got lost, sorry. To do that I think you need to move to a
function rather than a simple equation.
The function below should go in a general code module (i.e., not one
attached to a form, report or other db object). In the VBE:
Insert>Module.
Make sure the name of the module is NOT the same as any
function/procedure
you might put in it. modGeneralCode would do for a start.
You can call the function from a query as follows:
(unrounded result) Finish:
GetEndTime([StartFieldName],[HoursFieldName])
(rounded to nearest minute) Finish:
GetEndTime([StartFieldName],[HoursFieldName],True)
You can call the function from a Form or Report control as you would any
function:
(ControlSource property) =
GetEndTime([StartFieldName],[HoursFieldName])
'********************
Public Function GetEndTime(dtmStart As Date, dblElapsed As Double,
Optional
bolRound As Boolean = False) As Date
Dim dtmEnd As Date
Dim iMidnights As Integer
Dim iSeconds As Integer
dtmEnd = dtmStart + (dblElapsed / 24)
iMidnights = DateDiff("d", dtmStart, dtmEnd)
Do Until iMidnights = 0
' Add 5 hours at a time, regardless of how many midnights we've
crossed
' Test each result to see if we've crossed yet another midnight.
' Every 19 hour change to dblElapsed should change dtmEnd by a
full
Day.
dtmStart = dtmEnd
dtmEnd = DateAdd("h", 5, dtmEnd)
iMidnights = iMidnights - 1
If DateDiff("d", dtmStart, dtmEnd) > 0 Then
iMidnights = iMidnights + 1
End If
Loop
If bolRound = True Then
' Round results to nearest minute
iSeconds = Second(dtmEnd)
If iSeconds < 30 Then
' Round down to nearest minute
GetEndTime = DateAdd("s", -iSeconds, dtmEnd)
Else
' Round up to nearest minute
GetEndTime = DateAdd("s", (60 - iSeconds), dtmEnd)
End If
Else
' Don't round (the default)
GetEndTime = dtmEnd
End If
End Function
'********************
HTH,
Emily said:
"If possible I would also like to exclude the hours between midnight
and 5
AM."
I tried George's code and it worked fine. My only issue is how to
exclude
the 5 hours between 12 and 5. For example: one start time could be
9/1/2007
5:12 PM
Then you add 13.59 hours and the finish would be 9/2/2007 6:47:13 AM.
When
really it would be 5 hours later because of the hours when no one is
working.
And you can't just subtract the hours from all of the calculations
because
some of them occur within the 19 hours of the day.
Any ideas?
Thanks.
Emily
:
Yeah that will work too. I don't know where my mind was going last
night!
If you want to round it down to the last whole minute then you can use
this:
Result: 0.000694444*Int([Finish Time]/0.000694444)
Dates and Times are stored as Numbers. Each integer represents a day,
each
decimal represents a fraction of a day. To work out these fractions:
1 Day = 1
1 Hour = 1/24 = 0.041666666
1 Minute = 1/24/60 = 0.000694444
1 Second = 1/24/60/60 = 0.000011574
Each system we work with, be it Excel, Visual Basic, SQL Server has a
different Zero Date or Reference Date. To find out what it is, Format
0
(Zero) as a date. Then format Today's date as a number. You can see
the
number represents the number of days, hours and minutes etc. that have
passed
since the reference date.
Kind regards
Tim
:
Lets simplify things a bit:
Finish: [Start]+([Manufacturing LT]/24)
Be sure to apply the desired Date format to Finish.
HTH,
Tried that. Didn't work. Came up with this...
Start Manufacturing LT Finish
9/18/2007 4:45:00 PM 7.40857068108902 39351.1064873478
9/10/2007 5:15:00 PM 9.57489203519821 39345.2936420352
9/1/2007 5:12:00 PM 13.58692284599 39340.3035895127
8/28/2007 8:41:00 AM 10.1934295596542 39332.5552351152
Thanks for your patience.
:
Oops, missed of the remaining hours less than a day. Try this
Finish: [Start]+(Int([Manufacturing LT]/24))+([Manufacturing
LT]-(Int([Manufacturing LT]/24)*24)*0.04166)
Sorry short of time, let me know if this works. I will check in
the
morning.
Kind regards
Tim
:
This is what I put in my query field:
Finish: [Start]+(Int([Manufacturing LT]/24))+([Manufacturing
LT]-Int(([Manufacturing LT])*0.04166))
I had to add in a couple parentheses in order for it to work,
but
now
it
isn't coming up with the correct end date/time.
I will give you an example so maybe you can troubleshoot it:
Start Manufacturing LT Finish
9/18/2007 4:45:00 PM 7.40857068108902 9/26/2007 2:33:21 AM
9/10/2007 5:15:00 PM 9.57489203519821 9/20/2007 7:02:51 AM
9/1/2007 5:12:00 PM 13.58692284599 9/15/2007
7:17:10 AM
8/28/2007 8:41:00 AM 10.1934295596542 9/7/2007 1:19:32 PM
I believe it is counting my decimal hours as days. Can you fix
the
code?
Thanks,
Emily
:
Whoops, made a typo and missed a [ it should have read
[Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal
Hours]-INT([Decimal
Hours])*0.04166)
:
[Start Date Time] + (INT([Decimal Hours]/24)) + (([Decimal
Hours]-INT(Decimal
Hours])*0.04166)
Kind regards
Tim
:
Hello,
I have a start date and time which is inputed to a form
in
the
format of
##/##/#### HH:MM:SS
I also have a total time which is in decimal hours. Like
25.61.
How would I create an expression in a query to take the
start
date/time, add
the total time to it, and get the result in the date/time
format.
If possible I would also like to exclude the hours
between
midnight and 5 AM.
Thanks in advance,
Emily
