Angle of Time Value UDF -- Issue with Hour-Second Angle

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
264
Platform
  1. Windows
With the VBA converted code for the code at stackoverflow link Time Angles. I am able to output first two results correctly. But the third result output is not as expected.

For example, for time 11:54:29 the expected results are Hour-minute hands =30.34, Minute-second hands =152.90, and Second-hour hands =176.76.

The current output from udf is:
' Hour-Minute = 30.34166 -- CORRECT
' Minute-Second = 152.90 -- CORRECT
' Second-Hour = 183.24166 -- INCORRECT

VBA Code:
Function AngleOfTime(x As Date, i As Integer) As Double

    Dim h%, m%, s%, u, v, w

    h = Hour(x)
    m = Minute(x)
    s = Second(x)
    
    u = ((6 * m) + ((1 / 60 * 6) * s))    ' Angle of the minute hand.
    v = ((30 * h) + (0.5 * (m + s / 60)))    ' Angle of the hour hand.
    w = (6 * s)    ' Angle of the second hand.

    If i = 1 Then
        AngleOfTime = Abs(v - u)   ' Hour-minute Angle
    ElseIf i = 2 Then
        AngleOfTime = Abs(u - w)   ' Minute-second Angle
    ElseIf i = 3 Then
        AngleOfTime = Abs(w - v)   ' Second-hour Angle
    Else
    AngleOfTime = 0
    End If

End Function

Any possible fix?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,079
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
The expected result based on?
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,079
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I reviewed the link before I posted though. The basis of the expected results is not stated, so how do we know those answers are CORRECT

Maybe I am over-simplifying it though, but to get (Hour/Second), I simply do (Hour-Minute)+(Minute-Second), so i struggle with how the first 2 answers are right and the Third is INCORRECT

I hope you follow my Logic/Thinking
 

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
264
Platform
  1. Windows
@Momentman; I understand your point ... but the post starts with non-matching results, which after the code edits, as per the author, results in the exactly the same values, so.
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
... The current output from udf is: ...
' Second-Hour = 183.24166 -- INCORRECT...
The UDF output is correct.

For 11:54:29:
- the angle of the second hand is 29 / 60 * 360° = 174°
- the angle of the hour hand is 11 / 12 * 360° + 54 / 60 * 30° + 29 / 60 / 60 * 30° = 357.24°
- the hour-second handle is 357.24° - 174° = 183.24°
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,252
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top