Solved

differences in date time to include fractional minutes

Posted on 2010-08-27
14
392 Views
Last Modified: 2012-05-10
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
Comment
Question by:David627
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33542885
Yes, there is. DATEDIF
0
 
LVL 18

Expert Comment

by:Cluskitt
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

by:
Cluskitt earned 300 total points
ID: 33542922
0
 
LVL 17

Assisted Solution

by:calacuccia
calacuccia earned 200 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

by:calacuccia
calacuccia earned 200 total points
ID: 33542987
Some samples attached
david.xls
0
 
LVL 92

Expert Comment

by:Patrick Matthews
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

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 17

Expert Comment

by:calacuccia
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

by:barry houdini
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

by:barry houdini
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

by:David627
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

by:David627
ID: 33543432
BTW:

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

0
 
LVL 50

Expert Comment

by:barry houdini
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

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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now