Calculate Elapsed time with Datediff

[dhulett]

I have developed the following vb code to calculate elapsed minutes from two date/time entries in Excel. I need to tweek this a little further to calculate the elapsed time during the hours the store is open for business to track customer impact. I also have to account for the timezone the store is located in. Here is the code sample. Thanks in advance for your ideas.


Function CalcConceptMinutes(StartDate As Date, EndDate As Date, Concept As String) As Integer
Dim RowCntr As Integer
Dim RowCount As Integer
Dim intY As Integer
Dim TotMin As Long
Dim TFailed As Date
Dim TRestored As Date

RowCntr = 1
While Sheets(&quot;Raw Data&quot.Cells(RowCntr, 1) <> &quot;&quot;
RowCntr = RowCntr + 1
Wend
RowCount = RowCntr
TotMin = 0
RowCntr = 2
While RowCntr <= RowCount
If ((Sheets(&quot;Raw Data&quot.Cells(RowCntr, 5) = &quot;AT&T Regional&quot Or (Sheets(&quot;Raw Data&quot.Cells(RowCntr, 5) = &quot;AT&T Local&quot) Then
'do nothing
Else
GoTo Not_Telco
End If

TFailed = Sheets(&quot;Raw Data&quot.Cells(RowCntr, 2)
TRestored = Sheets(&quot;Raw Data&quot.Cells(RowCntr, 3)
If Sheets(&quot;Raw Data&quot.Cells(RowCntr, 6) = Concept Then
If TFailed = Null And (TRestored >= StartDate) And (TRestored <= EndDate) Then
TotMin = TotMin + DateDiff(&quot;n&quot;, StartDate, TRestored)
ElseIf TRestored = Null And (TFailed >= StartDate) And (TFailed <= EndDate) Then
TotMin = TotMin + DateDiff(&quot;n&quot;, TFailed, EndDate)
ElseIf (TFailed >= StartDate) And (TFailed <= EndDate) And (TRestored >= StartDate) And (TRestored <= EndDate) Then
TotMin = TotMin + DateDiff(&quot;n&quot;, TFailed, TRestored)
'2 lines below addedd 12/31/01
ElseIf (TFailed >= StartDate) And (TFailed <= EndDate) And (TRestored >= StartDate) And (TRestored >= EndDate) Then
TotMin = TotMin + DateDiff(&quot;n&quot;, TFailed, EndDate)
End If
End If

Not_Telco:

RowCntr = RowCntr + 1
Wend
CalcConceptMinutes = TotMin
End Function