PDA

View Full Version : Calculate hours between two times

Jennifer
09-14-2015, 04:33 PM
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
09-14-2015, 06:17 PM
Format the results column as "[h].00" for decimal hours or "[h]:mm" for Hours:Minutes
Use the Formula =ABS(B1-A1)

Paul_Hossler
09-14-2015, 06:44 PM
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
09-14-2015, 07:51 PM
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
09-15-2015, 07:49 AM
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
09-15-2015, 08:16 AM
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
09-15-2015, 02:38 PM
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
09-16-2015, 03:26 PM
@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
09-17-2015, 09:20 AM
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.