Calculate hours between two times

[Jennifer]

I need to calculate the number of hours between two date-times each having the format [DayOfWeek hh:mm].

For example,

Start	End	Hours
Mon 6:00 pm	Mon 9:00 pm	3.00
Tue 7:30 am	Tue 4:00 pm	8.50
Wed 5:45 pm	Thu 8:00 am	14.25
Thu 5:00 pm	Sun 5:00 pm	72.00
Fri 12:00 pm	Fri 12:00 pm	168.00
Fri 6:00 pm	Fri 5:00 pm	167.00

If the days are the same,

If End time > Start time, then Result = End time - Start time.
Else Result = (6 * 24) + (24 - (Start time - End Time))

Else Result = (24 * number of whole days between date times) + (24 - Start time) + (End time)

Is there any kind of a spreadsheet function that will do that? I'd guess not. At least, I can't find one.

So I plan to write a UDF using the logic above.

Any better suggestions?

[SamT]

Format the results column as "[h].00" for decimal hours or "[h]:mm" for Hours:Minutes
Use the Formula =ABS(B1-A1)

[Paul_Hossler]

Are the values (e.g. "Mon 6:00 pm") entered as strings, or as date/times and then formatted to that format?


A1 is a string, B1 is the Double behind a Date, and C1 is formatted for display like that

I suspect that the entries are entered as Text

Capture.JPG

[Jennifer]

Are the values (e.g. "Mon 6:00 pm") entered as strings, or as date/times and then formatted to that format?

I'm entering them myself from the keyboard, so they can be whatever they need to be. That is, I am willing to be restricted to a fixed input format.

Did I understand the question?

[Paul_Hossler]

I'm 99% sure that will make them Text, and as they stand you can't do simple math or formatting with them

A UDF is probably required in order to easily calculate. Try the attachment

The code could be made more modular (and probably more clever-er) but I went with the more straight forward approach

Option Explicit
    'If the days are the same,
       'If End time > Start time, then
          ' Result = End time - Start time.
      'Else
        'Result = (6 * 24) + (24 - (Start time - End Time))
    ' Endif
    'Else
    'Result = (24 * number of whole days between date times) + (24 - Start time) + (End time)
    'Endif

Function NumberOfHours(STime As String, ETime As String) As Double
    Const cDay As Long = 0
    Const cTime As Long = 1
    Const cAMPM As Long = 2
    Const cDays As String = "MONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISATSUN"
    Dim vStart As Variant, vEnd As Variant
    Dim sDayStart As String, sDayEnd As String
    Dim dTimeStart As Double, dTimeEnd As Double
    Dim iDayStart As Long, iDayEnd As Long, iNumDays As Long
    vStart = Split(STime, " ")
    sDayStart = UCase(vStart(cDay))
    'need the 'extra' ( ) around the parameters to coerce the type
    dTimeStart = HHMM2DecimalHours((vStart(cTime)), (vStart(cAMPM)))
    vEnd = Split(ETime, " ")
    sDayEnd = UCase(vEnd(cDay))
    'need the 'extra' ( ) around the parameters to coerce the type
    dTimeEnd = HHMM2DecimalHours((vEnd(cTime)), (vEnd(cAMPM)))
    If vStart(cDay) = vEnd(cDay) Then
        If dTimeEnd > dTimeStart Then
            NumberOfHours = dTimeEnd - dTimeStart
        Else
            NumberOfHours = (6# * 24#) + (24# - (dTimeStart - dTimeEnd))
        End If
    Else
         iDayStart = InStr(cDays, sDayStart)
          iDayEnd = InStrRev(cDays, sDayEnd)
          iNumDays = ((iDayEnd - iDayStart) / 3) - 8
          NumberOfHours = (24# * iNumDays) + (24# - dTimeStart) + dTimeEnd
    End If
End Function

Private Function HHMM2DecimalHours(S As String, AMPM As String) As Double
    Dim i As Long
    Dim s1 As String
    Dim d As Double
    s1 = Trim(S)
    i = InStr(s1, ":")
    If i = 0 Then
        d = CDbl(s1)
    Else
        d = CDbl(Left(s1, Len(s1) - i - 1)) + CDbl(Right(s1, Len(s1) - i)) / 60#
    End If
    If UCase(AMPM) = "PM" Then d = d + 12
    HHMM2DecimalHours = d
End Function

[Jennifer]

I'm 99% sure that will make them Text, and as they stand you can't do simple math or formatting with them

If I enter it as one string ("Mon 6:45 pm"), then it will be text for sure. But if it helps, I'm willing to enter it in two separate cells. If I put "Mon" in A1 and "6:45 pm" in A2, A1 will be text, but A2 will be numeric (0.78125).

A UDF is probably required in order to easily calculate. Try the attachment

The code could be made more modular (and probably more clever-er) but I went with the more straight forward approach

Wow. Thank you so much. I really appreciate it. That will give me something to do for the next couple of days!

[SamT]

I'm entering them myself from the keyboard, so they can be whatever they need to be. That is, I am willing to be restricted to a fixed input format.

Enter them as 3-sep-15 8:30AM, (d-mmm-yy hr:min AMPM,) then format those columns with "ddd h:mm am/pm."

By entering the date/time values in that format, Excel will store them in Date Serial format. Formatting the cells anyway you want will not affect the stored Date Serial values.

You can test this: Paste" 3-Sep-15 8:30AM" into A1, Paste" 3-Sep-15 2:30PM" into B1, "=ABS(A1-B1)" into C1, and "=ABS(B1-A1)" into D

In A2 enter "=A1" and drag it across into D2.

Format A1 and B1 as any Date/Time, C1-D1 as "[h].00". Format A2-D2 as a Number with 11 digits.

Note: 6 hrs is 0.25 days; "[h].00" displays to 1/100th hr or 36 seconds; "[h].000" displays to 1/1000th hr or 3.6 seconds.

[Paul_Hossler]

@SamT --

Note: 6 hrs is 0.25 days; "[h].00" displays to 1/100th hr or 36 seconds; "[h].000" displays to 1/1000th hr or 3.6 seconds.

Not sure I'm seeing the [h].00 formatting displaying to 1/100th hr

Capture.JPG

[SamT]

My help file is a little fuzzy on that point. But, it appears that you are correct. I can't get a decimal hours display either. I had to use Time*24 and format it as a two decimal number. Thanks for pointing this out to me. I edited the help file to reflect this.

To correct my previous postings

Paste" 3-Sep-15 8:30AM" into A1, Paste" 3-Sep-15 2:30PM" into B1, "=ABS(A1-B1)" into C1, and "=ABS(B1-A1)" into D

Format A1 and B1 as any Date/Time, C1-D1 as "[h].00". Format A2-D2 as a Number with 11 digits.

Should read

Paste" 3-Sep-15 8:30AM" into A1, Paste" 3-Sep-15 2:30PM" into B1, "=ABS(A1-B1)*24" into C1, and "=ABS(B1-A1)*24" into D

Format A1 and B1 as any Date/Time, C1-D1 as Number with 2 decimals.