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

Who is Participating?

Improve company productivity with a Business Account.Sign Up

Yes, there is. DATEDIF
Sorry, posted by mistake.

Yes, there is. DATEDIF(). However, it will only calculate Date, not Time. This might be a good starting point:
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


calacucciaConnect With a Mentor Commented:
Some samples attached
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
    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")
                If Not Dict.Exists(Format(x, "m/d/yyyy")) Then Dict.Add Format(x, "m/d/yyyy"), Format(x, "m/d/yyyy")
            End If
    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
            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
    ' convert days to hours
    WorkingHrs = WorkingHrs * 24
    On Error GoTo 0
    Set Dict = Nothing
End Function

Open in new window

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
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.
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
....but to cater for the odd entry that goes across multiple days you can use this version
formatting as d:hh:mm works fine unless the day range exceeds 31
regards, barry
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

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

David627Author Commented:

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

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
ineed, uu is my regional version of hh, d:hh:mm does work
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.