# 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
###### Who is Participating?

x

Commented:
0

Commented:
Yes, there is. DATEDIF
0

Commented:
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

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

Commented:
Some samples attached
david.xls
0

Commented:
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
``````
0

Commented:
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

Commented:
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

Commented:
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

Commented:
...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

Author 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

Author Commented:
BTW:

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

0

Commented:
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

Commented:
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.