Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# differences in date time to include fractional minutes

Posted on 2010-08-27
Medium Priority
403 Views
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
0
Question by:David627
• 4
• 3
• 3
• +2

LVL 18

Expert Comment

ID: 33542885
Yes, there is. DATEDIF
0

LVL 18

Expert Comment

ID: 33542911
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

LVL 18

Accepted Solution

Cluskitt earned 1200 total points
ID: 33542922
0

LVL 17

Assisted Solution

calacuccia earned 800 total points
ID: 33542953
Datedif only works for days, months, years though.

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

d:uu:mm

0

LVL 17

Assisted Solution

calacuccia earned 800 total points
ID: 33542987
Some samples attached
david.xls
0

LVL 93

Expert Comment

ID: 33543038
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

LVL 93

Expert Comment

ID: 33543065
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

LVL 17

Expert Comment

ID: 33543066
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

LVL 50

Expert Comment

ID: 33543205
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

LVL 50

Expert Comment

ID: 33543322
...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 Comment

ID: 33543383
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 Comment

ID: 33543432
BTW:

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

0

LVL 50

Expert Comment

ID: 33543823
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

LVL 17

Expert Comment

ID: 33543986
ineed, uu is my regional version of hh, d:hh:mm does work
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable â€¦
Screencast - Getting to Know the Pipeline
###### Suggested Courses
Course of the Month10 days, 16 hours left to enroll