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

14393

[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. :clap: That will give me something to do for the next couple of days! :bug:

[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

14406

[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.