Hi Douglas,
I'm sorry, I reverted back to my work code, since I messed everything up...
but I was sitting here just now ... I've done all my previous calcuations
using TimeSerial()... all the math is being done in decimal format, no?
So... why wouldn't I just stop formating the fields in Access as date and
time and just set it to decimal? and then that way i just dump it into Excel
and let it do it's thing with the time duration (from a serial point of view).
Any hoo, here is the code that I am currently using in Excel no problems, I
have some custom functions being used, do you need that as well?
Function CalcBusinessHours(CaseNumber2 As String, CaseNumber1 As String,
Severity As Integer, _
NewValue2 As String, NewValue1 As String, LTActionAccept As Date,
LTActionArrive As Date)
' Author Mike S. Avelar
' Version v2.0
' Company Hewlett-Packard (Canada) Co.
' Copyright Hewlett-Packard (Canada) Co. 2006.
' This was developed for the purpose of the Nortel WISE Contract to
' measure the SLO between Hewlett-Packard (Canada) Co. and SITEL
' This application takes into account business hours: M-F; 08h00-17h00
' Declaring variables
Dim LTAArrive As Date
Dim LTAAccept As Date
Dim RemoveWeekends As Date
Dim StartTime As Date
Dim EndTime As Date
Dim StartDay As Date
Dim EndDay As Date
Dim BusinessHours As Date
' Make sure that the Accepted time is greater than the Arrival time
' This is a precaution; this should happen if the data was prepared
correctly
If LTActionArrive < LTActionAccept Then
LTAArrive = LTActionArrive
LTAAccept = LTActionAccept
Else
LTAArrive = LTActionAccept
LTAAccept = LTActionArrive
End If
' Serialize the day
StartDay = DateSerial(DatePart("yyyy", LTAArrive), DatePart("m",
LTAArrive), DatePart("d", LTAArrive))
EndDay = DateSerial(DatePart("yyyy", LTAAccept), DatePart("m",
LTAAccept), DatePart("d", LTAAccept))
If CaseNumber2 = CaseNumber1 Then
If NewValue2 = "DESKTOP-NONHARDWARE-OPER" And NewValue1 = "SUBMIT"
Then
CalcBusinessHours = TimeSerial(0, 0, 0)
ElseIf NewValue2 = "DESKTOP-NONHARDWARE-OPER" Or NewValue2 =
"RE-OPENED" Then
CalcBusinessHours = TimeSerial(0, 0, 0)
Else
If (NewValue1 = "SUBMIT" Or NewValue1 =
"DESKTOP-NONHARDWARE-OPER" Or NewValue1 = "RE-OPENED") Then
' Call custom functions to assign values to variables to
make the business hour calculations
RemoveWeekends = TimeSerial((RemoveWeekendDays(LTAArrive,
LTAAccept) * 9), 0, 0)
StartTime = CalcStartTime(LTAArrive)
EndTime = CalcEndTime(LTAAccept)
If EndDay = StartDay Then
BusinessHours = SameDayWorkHours(LTAArrive, LTAAccept)
Else
BusinessHours = RemoveWeekends + StartTime + EndTime
End If
CalcBusinessHours = BusinessHours
End If
End If
Else
CalcBusinessHours = TimeSerial(0, 0, 0)
End If
End Function
Douglas J. Steele said:
What's your code look like?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Mike said:
Okay, so my confusion has grown a bit...
I have this VBA that works perfect in Excel for obvious reasons and
doesn't
in Access. It removes weekend days, and only calculates duration of time
during 08h00 to 17h00. Works great.
Now, I've tried to do the calcs to turn everthing into seconds, but I keep
getting 'overrun' errors... on variant data types?? Even if I employ the
CDec() function... Also, since I developed it in Excel 2003 (and use it in
2007), I did all calculations using TimeSerial() function. I was using
DatePart() to get the hours (multiply that by 60, then by 60 to get the
seconds), DatePart() to get the minutes (*60 to get seconds), and
DatePart()
to get the seconds... so I would then have 3 whole numbers (all in
seconds)
to add together to then output to the query table (which actually apends
to a
table with a field data type of Decimal).
I know this is a very simple problem, but I can't seem to see the forest
from the trees today.
Douglas J. Steele said:
No, you can't.
A date/time field is an 8 byte floating point number, where the integer
portion represents the date as the number of days relative to 30 Dec,
1899,
and the decimal portion represents the time as a fraction of a day. That
means that the Date data type is really only meant for timestamps, not
durations, and therefore the hours can never exceed 24.
The recommended approach to working with durations is to convert the time
to
a long integer representing the total number of units (seconds?
minutes?),
do the arithmetic, then convert back.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi Everyone!
In Excel I can format time like so: [h]:mm:ss which will allow me to
see
time as 37:33:28 - how do I do this in Access?? Because this doesn't
work -
which really confuses me...
Cheers,
Mike
