#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Location
    Mexico, DF
    Posts
    249
    Rep Power
    12

    Exclamation Calculate time difference between two dates


    Hi folks!,

    I need a script which can do this:
    - Calculate the difference in minutes between two date/time.

    I now... you are going to say that i need to use de datediff function, but here is the trick.

    I need to calculate the time based on working hours using working days and time.

    For example, suppose that you work monday to friday from 9:00am to 6:00pm and you have a task and you need to know how much time have you spent on it.

    The task arrived at your email at 7:00pm on Sunday... so you will start to work on it at 9:00 am on Monday.

    If you finish the job on Wednesday at 10:00 you had spent 19 hours on the project... datediff will say 63 hours.

    Regards. German
    PS: Sorry for any grammar or syntax error, englush it's not my native language.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Location
    Mexico, DF
    Posts
    249
    Rep Power
    12

    I Did It


    I did it !!!

    If someone else wants the code, just tell me.

    Regards. German
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2004
    Posts
    1
    Rep Power
    0

    Date & time difference


    Hi,

    Can you send me the code? I would be grateful. Thanks and have a nice day. You can send it to tookc.external@infineon.com


    Originally Posted by microbati
    I did it !!!

    If someone else wants the code, just tell me.

    Regards. German
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2005
    Posts
    1
    Rep Power
    0

    Calculate time difference between two dates in working hours


    Can you send me the code ?
    my e-mail is : ertekinm@gmail.com
    Thank you


    Originally Posted by microbati
    I did it !!!

    If someone else wants the code, just tell me.

    Regards. German
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2005
    Posts
    1
    Rep Power
    0

    Smile Date difference code


    Can you please send me this code? Thanks!
    rare_bird@hotmail.com
  10. #6
  11. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2004
    Location
    Alaska
    Posts
    588
    Rep Power
    176
    can you send me a copy of the code, I have a function that is similar to this for an employee clock in/clock out program. I will be interested to compare notes.

    matt@lightwirestudios.com

    Matt
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Location
    Mexico, DF
    Posts
    249
    Rep Power
    12

    The CODE


    Hi Folks,

    Sorry, i forgot to subscribe to this thread and i didn't get any notification, but here is the code... Some of the functions names are in spanish, but you should be able to use it anyway.

    Code:
    Public Function SiguienteDiaHabil(fecha As Date, Coverage As Integer) As Date
        'Possible Values for Coverage:
        '5 - Mon to Fri
        '6 - Mon to Sat
        '7 - Mon to Mon
        Select Case Coverage
            Case 5
                SiguienteDiaHabil = CDate(RemoveTime(fecha)) + 1
                wd = Weekday(fecha + 1)
                If wd = vbSunday Then SiguienteDiaHabil = CDate(RemoveTime(fecha)) + 2
                If wd = vbSaturday Then SiguienteDiaHabil = CDate(RemoveTime(fecha)) + 3
            Case 6
                SiguienteDiaHabil = CDate(RemoveTime(fecha)) + 1
                wd = Weekday(fecha + 1)
                If wd = vbSunday Then SiguienteDiaHabil = CDate(RemoveTime(fecha)) + 2
            Case 7
                SiguienteDiaHabil = CDate(RemoveTime(fecha)) + 1
        End Select
    End Function
    
    Public Function AnteriorDiaHabil(fecha As Date, Coverage As Integer) As Date
        'Possible Values for Coverage:
        '5 - Mon to Fri
        '6 - Mon to Sat
        '7 - Mon to Mon
        Select Case Coverage
            Case 5
                AnteriorDiaHabil = CDate(RemoveTime(fecha)) - 1
                wd = Weekday(fecha - 1)
                If wd = vbSunday Then AnteriorDiaHabil = CDate(RemoveTime(fecha)) - 3
                If wd = vbSaturday Then AnteriorDiaHabil = CDate(RemoveTime(fecha)) - 2
            Case 6
                AnteriorDiaHabil = CDate(RemoveTime(fecha)) - 1
                wd = Weekday(fecha - 1)
                If wd = vbSunday Then AnteriorDiaHabil = CDate(RemoveTime(fecha)) - 2
            Case 7
                AnteriorDiaHabil = CDate(RemoveTime(fecha)) - 1
        End Select
    End Function
    
    Public Function EsDiaHabil(fecha As Date, Coverage As Integer) As Boolean
        'Possible Values for Coverage:
        '5 - Mon to Fri
        '6 - Mon to Sat
        '7 - Mon to Mon
        EsDiaHabil = True
        Select Case Coverage
            Case 5
                If Weekday(fecha) = vbSunday Or Weekday(fecha) = vbSaturday Then EsDiaHabil = False
            Case 6
                If Weekday(fecha) = vbSunday Then EsDiaHabil = False
        End Select
    End Function
    
    Function RemoveTime(fecha As Date) As String
        If InStr(fecha, " ") = 0 Then RemoveTime = CStr(fecha) + " " Else RemoveTime = CStr(Mid(fecha, 1, InStr(fecha, " ")))
    End Function
    
    'This is the function that you should USE!
    'f0 = First or start date (date)
    'f1 = Last or end date (date)
    'CovDays =  5 - Mon to Fri / 6 - Mon to Sat / 7 - Mon to Mon
    'CovHour = The coverage hours (Example: "8:30-18:30") Must be 24 hours format!
    'Return the number of seconds between f1 and f0.
    
    Public Function TResp2(f0 As Date, f1 As Date, CovDays As Integer, CovHour As String) As Long
    
    Dim CovHour0 As String, CovHour1 As String
    Dim f0CS, f1CE, f0CE, f1CS, fStart, fEnd, currentDate As Date
    Dim beginAdd, endAdd, timePerDay, Cont As Long
    
    
    If f0 >= f1 Then
        TResp2 = 0
        Exit Function
    End If
        
    
    If CStr(f0) <> "" And CStr(f1) <> "" And CStr(CovDays) <> "" And CStr(CovHour) <> "" Then
        num = InStr(CovHour, "-")
        CovHour0 = Mid(CovHour, 1, num - 1)
        CovHour1 = Mid(CovHour, num + 1, Len(CovHour))
        f0CS = CDate(RemoveTime(f0) + CStr(CovHour0))
        f1CE = CDate(RemoveTime(f1) + CStr(CovHour1))
        
        f0CE = CDate(RemoveTime(f0) + CStr(CovHour1))
        f1CS = CDate(RemoveTime(f1) + CStr(CovHour0))
        
        If Day(f0) = Day(f1) And Month(f0) = Month(f1) And Year(f0) = Year(f1) Then
            If (EsDiaHabil(f0, CovDays)) Then
                fStart = f0
                fEnd = f1
                If f0 < f0CS Then fStart = f0CS
                If f0 > f1CE Then fStart = f1CE
                If f1 > f1CE Then fEnd = f1CE
                If f1 < f0CS Then fEnd = f0CS
                
                TResp2 = CLng(DateDiff("s", fStart, fEnd))
            Else
                TResp2 = 0
            End If
            Exit Function
        End If
        
        If EsDiaHabil(f0, CovDays) And f0 < f0CE Then
            fStart = f0
            If f0 < f0CS Then fStart = f0CS
            'Al comienzo añadir la diferencia
            beginAdd = CLng(DateDiff("s", fStart, f0CE))
        Else
            beginAdd = 0
        End If
        fStart = CDate(CStr(SiguienteDiaHabil(f0, CovDays)) + " " + CovHour0)
        
        If EsDiaHabil(f1, CovDays) And f1 > f1CS Then
            fEnd = f1
            If f1 > f1CE Then fEnd = f1CE
            'Al comienzo añadir la diferencia
            endAdd = CLng(DateDiff("s", f1CS, fEnd))
        Else
            endAdd = 0
        End If
        fEnd = CDate(CStr(AnteriorDiaHabil(f1, CovDays)) + " " + CovHour1)
        
        timePerDay = DateDiff("s", f0CS, f0CE)
        Cont = 0
        currentDate = fStart
        
        While (currentDate <= fEnd)
            Cont = Cont + timePerDay
            currentDate = SiguienteDiaHabil(currentDate, CovDays)
        Wend
            
        TResp2 = beginAdd + Cont + endAdd
    End If
    
    End Function
    
    'And you can use this function to convert the result from TResp2 into a valid Excel Date
    'Will be returned in the format: hh:mm:ss
    Public Function formatoHORA(cad As Long) As String
        Dim hora, minu, seg As String
        hora = CStr(Int(cad / 60 / 60))
        minu = CStr(Int((cad / 60)) Mod 60)
        seg = CStr(Int(cad Mod 60))
        If Len(hora) > 2 Then
            formatoHORA = Mid("000" + hora, Len(hora) + 1, 3) + ":" + Mid("00" + minu, Len(minu) + 1, 2) + ":" + Mid("00" + seg, Len(seg) + 1, 2)
        Else
            formatoHORA = Mid("00" + hora, Len(hora) + 1, 2) + ":" + Mid("00" + minu, Len(minu) + 1, 2) + ":" + Mid("00" + seg, Len(seg) + 1, 2)
        End If
    End Function
    If you have any doubts, please let me know.

    Regards. Germán.

IMN logo majestic logo threadwatch logo seochat tools logo