differences in date time to include fractional minutes

I am looking to calculate the difference in two dates listed as 1/4/2010  6:15:12 AM and show minutes for just working days

So if I had 1/4/2010  6:15:12 AM and 1/4/2010  6:46:00 AM (which is mm:dd:ss) I want it to show 31 minutes
If I have 6/14/2010 13:34 and 6/15/2010 12:02 I want it to show 1 day 23 hours and xx minutes

I'd like it to show it in dd:hh:mm  (1:23:15) format

I've tried NETWORKINGDAYS - is there something I can nest in that formula or is there another formula that can be used.

I know how to use the datediff function in Access but there is no function like it in Excel 2007.  I would prefer a formula however I would add VBA code - however formula is preferred as it is for another user.

Sample data attached

Thanks
David
david.xls
David627Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
CluskittCommented:
Yes, there is. DATEDIF
0
 
CluskittCommented:
Sorry, posted by mistake.

Yes, there is. DATEDIF(). However, it will only calculate Date, not Time. This might be a good starting point:
http://www.cpearson.com/excel/DateTimeWS.htm
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
calacucciaConnect With a Mentor Commented:
Datedif only works for days, months, years though.

You can simply substract both times (=C3-C1) and use custom format

d:uu:mm

0
 
calacucciaConnect With a Mentor Commented:
Some samples attached
david.xls
0
 
Patrick MatthewsCommented:
David627,Here is another approach, using a UDF I developed for tracking "working hours".  I am assuming that by "work days" you mean Mon-Fri, and that any times during those days are potentially working hours.  Both of those can be controlled by passing the right argument values.If your start date/time is in A2 and end date/time in B2, then this formula seems to be working:=INT(WorkingHrs(A2,B2,0,1,23456)/24)&TEXT(WorkingHrs(A2,B2,0,1,23456)/24,""":""hh:mm")Note that it returns the result in d:hh:mm, and as a text string.There is a problem with the examples from your question, though.Consider:6/14/2010 13:346/15/2010 12:02You had the result from that being 1:23:15.  Seeing as the total time elapsed in that example is 22 hours 28 minutes, I am puzzled by your example :)BTW, the function allows you to pass holidays as well.If you want to restrict working hours to, say, between 8am and 6pm, change the formula to:=INT(WorkingHrs(A2,B2,"8:00 AM","6:00 PM",23456)/24)&TEXT(WorkingHrs(A2,B2,0,1,23456)/24,""":""hh:mm")If you want to make "working days" be, say, Tue-Sat, change to:=INT(WorkingHrs(A2,B2,0,1,34567)/24)&TEXT(WorkingHrs(A2,B2,0,1,23456)/24,""":""hh:mm")Patrick
Function WorkingHrs(StartAt As Date, EndAt As Date, WorkStart As Date, WorkEnd As Date, Workdays As String, _
    ParamArray Holidays())

    ' Function calculates working hours available within a specified datetime range, allowing for
    ' scheduled working hours, non-working days, and holidays (if desired)
    
    ' Workdays specifies days employees normally work.  For example, to use Mon - Fri, use 23456.
    ' To do just Tue & Thu, use 35; etc.
    
    ' Assumes scheduled working hrs are the same on each working day!
    
    ' To use this function to calculate "working days", then simply divide the result from the function by
    ' the length of a standard workday in hours.
    
    Dim Counter As Long
    Dim Dict As Object
    Dim x As Variant
    Dim y As Variant
    Dim Days(1 To 7) As Boolean
    Dim WorkThisDay As Boolean
    Dim HolThisDay As Boolean
    Dim DateToday As Date
    Dim DayStart As Date
    Dim DayEnd As Date
    
    ' array indicates whether that weekday is a regular workday.  Initialize to False
    Days(1) = False
    Days(2) = False
    Days(3) = False
    Days(4) = False
    Days(5) = False
    Days(6) = False
    Days(7) = False
    
    ' populate array with results from Workdays argument
    For Counter = 1 To Len(Workdays)
        Days(Val(Mid(Workdays, Counter, 1))) = True
    Next
    
    On Error GoTo Cleanup
    
    ' populate holiday array
    If Not IsMissing(Holidays) Then
        Set Dict = CreateObject("Scripting.Dictionary")
        For Each x In Holidays
            ' Each element of Holidays may itself be an array (or an Excel range with >1 cell).  Test for that,
            ' and iterate through the elements of *that* array if needed.  If not, then simply process the
            ' current element
            If IsArray(x) Then
                For Each y In x
                    If Not Dict.Exists(Format(y, "m/d/yyyy")) Then Dict.Add Format(y, "m/d/yyyy"), Format(y, "m/d/yyyy")
                Next
            Else
                If Not Dict.Exists(Format(x, "m/d/yyyy")) Then Dict.Add Format(x, "m/d/yyyy"), Format(x, "m/d/yyyy")
            End If
        Next
    End If
    
    'loop through days in datetime range
    For Counter = Int(StartAt) To Int(EndAt)
        DateToday = CDate(Counter)
        ' determine if regular workday
        WorkThisDay = Days(Weekday(DateToday, vbSunday))
        ' determine if holiday
        If IsMissing(Holidays) Then
            HolThisDay = False
        Else
            If Dict.Exists(Format(DateToday, "m/d/yyyy")) Then HolThisDay = True Else HolThisDay = False
        End If
        ' if regular workday and not a holiday, figure out hrs from that day
        If WorkThisDay And Not HolThisDay Then
            ' starts and ends on same day
            If Int(StartAt) = Int(EndAt) Then
                DayStart = IIf(CDate(StartAt - Int(StartAt)) > WorkStart, CDate(StartAt - Int(StartAt)), WorkStart)
                DayEnd = IIf(CDate(EndAt - Int(EndAt)) < WorkEnd, CDate(EndAt - Int(EndAt)), WorkEnd)
                WorkingHrs = IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
            ' first day, if first day <> last day
            ElseIf Counter = Int(StartAt) Then
                DayStart = IIf(CDate(StartAt - Int(StartAt)) > WorkStart, CDate(StartAt - Int(StartAt)), WorkStart)
                DayEnd = WorkEnd
                WorkingHrs = IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
            ' days in between start and end, if any
            ElseIf Counter > Int(StartAt) And Counter < Int(EndAt) Then
                WorkingHrs = WorkingHrs + CDbl(WorkEnd - WorkStart)
            ' last day, if first day <> last day
            ElseIf Counter = Int(EndAt) Then
                DayStart = IIf(CDate(EndAt - Int(EndAt)) > WorkStart, WorkStart, CDate(EndAt - Int(EndAt)))
                DayEnd = IIf(CDate(EndAt - Int(EndAt)) < WorkEnd, CDate(EndAt - Int(EndAt)), WorkEnd)
                WorkingHrs = WorkingHrs + IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
            End If
        End If
    Next
    
    ' convert days to hours
    WorkingHrs = WorkingHrs * 24
    
Cleanup:
    On Error GoTo 0
    Set Dict = Nothing
    
End Function

Open in new window

0
 
Patrick MatthewsCommented:
calacuccia,Wow, I didn't think that was possible in a custom date format :)That simplifies my formula dramatically:=WorkingHrs(A2,B2,0,1,23456)/24applying a custom number format of:d:hh:mmPatrick
0
 
calacucciaCommented:
As I did not understand how 6/14/2010 13:34 and 6/15/2010 12:02 can be 1 day 23 h & someting, while it is 0 days, 22 hours and 28 minutes, I have interpreted as a simple substraction, if I'm wrong, just tell us.
0
 
barry houdiniCommented:
You can use NETWORKDAYS with some modification
I note that nearly every example in the attached file has a start and end time on the same date. If that was always the case you could just use
=NETWORKDAYS(A2,A2)*(C2-A2)
....but to cater for the odd entry that goes across multiple days you can use this version
=NETWORKDAYS(A2,C2)-1+IF(NETWORKDAYS(C2,C2),MOD(C2,1),1)-NETWORKDAYS(A2,A2)*MOD(A2,1)
formatting as d:hh:mm works fine unless the day range exceeds 31
regards, barry
0
 
barry houdiniCommented:
...sorry, I didn't look far enough, I see the first 3000 records are all on the same day and then you get longer periods....still my second suggestion will work to give you working days/hours/mins between the date/times, see attached
If you have time periods longer than 31 days then I'd have to adjust the formula along the same lines that Patrick showed.....
regards, barry

26434540.xls
0
 
David627Author Commented:
my calculation was wrong however I think you can figure it out - thanks to those that did and gave me some excellent solutions!

Thanks again

David
0
 
David627Author Commented:
BTW:

calacuccia: format of d:uu:mm doesn's seem to work

0
 
barry houdiniCommented:
I assume that d:uu:mm is a regional variation of d:hh:mm.....

.....didn't you want to count just working hours.....or would that equate to all the elapsed time, even on Saturdays and Sundays?

Regards, barry
0
 
calacucciaCommented:
ineed, uu is my regional version of hh, d:hh:mm does work
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.