Clock Face Angles

Posted on by Dick Kusleika

Old Clock

Financial RAD Developer quotes this interview question

Please tell me what you would implement to tell me the angle between then hands on a watch face at any given time of the day.

Here’s my worksheet function to convert time to angle

=ABS(((MOD(HOUR(G2),12)/HoursPerDay*DegreesInCircle)+((DegreesInCircle/HoursPerDay)*MINUTE(G2)/MinutesPerHour))-DegreesInCircle/MinutesPerHour*MINUTE(G2))

And my VBA

Used as

=timeangle(G1)

A little verbose, I’ll admit.

21 thoughts on “Clock Face Angles”

  1. With the date/tiem in B2:

    The hour hand goes round twice a day (720 degrees) so we multiply the fractional day by 720 and mod it by 360 to get the angle:

    =MOD((MOD(B2,1)*720),360)

    And the minute hand goes round 24 times (8640 degrees):

    =MOD((MOD(B2,1)*8640),360)

    So the difference is:

    =ABS(MOD((MOD(B2,1)*720),360) – MOD((MOD(B2,1)*8640),360))

    This has the advantage of including seconds, which will very slowly advance the hour and minute hands.

    Of course, this gives an answer between 0 and 359 degrees, whereas we want one between 0 and 180 degrees – 270 degrees should be 90.
    I can’t think in my head how to convert 270 to 90 (and 359 to 1) without a nasty IF statement.

  2. Hrm, it looks like this will work:

    =ABS(180-MOD(ABS(MOD((MOD(B2,1)*720),360) – MOD((MOD(B2,1)*8640),360))+180,360))

    If we put numbers into Dick’s formula:

    =ABS(((MOD(HOUR(G2),12)/HoursPerDay*DegreesInCircle)+((DegreesInCircle/HoursPerDay)*MINUTE(G2)/MinutesPerHour))-DegreesInCircle/MinutesPerHour*MINUTE(G2))

    Then the hour angle is a factor of two out, as HoursPerDay needs to be 12 in his code, not the 24 of reality!

  3. Kind of an age-discrimination question (on the young end). I can see younger people saying “What’s a hand on a watch face?”

    Hung some analog clocks in my house recently. The kids hate them.

  4. Here’s the formula I came up with:

    =ABS(HOUR(G2)*DegreesPerHour-MINUTE(G2)*DegreesPerMinute)

    Where
    DegreesPerHour = 360/12 = 30
    and
    DegreesPerMinute = 360/60 = 6.

  5. A little verbose, I’ll admit.

    Just a little! How about?:

    Function Timeangle(TimeNum As Double) As Double
    Timeangle = Abs((TimeNum * 2 – Int(TimeNum * 2)) – (TimeNum * 24 – Int(TimeNum * 24))) * 360
    End Function

    But there is a valuable lesson in there. The VB mod function converts any input values to integers. I don’t know what genius decided it should work that way, but it’s a nasty little trap for the unwary.

  6. Dick – your formulation gives the same answer for 4:32:00 and 4:32:30. The hour hand moves 1/4 deg in 30 seconds, and the minute hand moves 3 deg at the same time.

    Dick and Doug – both formulations give the “wrap-around” angle for a time like 12:55 (302.5 deg).

    Using that the hour hand moves 30 degrees/hour and the minute hand 360 degrees/hour, and breaking it into three steps for clarity, assume the time is in G2: = 12:55 PM

    H2: = IF((G2-INT(G2))*24>=12,(G2-INT(G2))*24-12,(G2-INT(G2))*24)*30

    or 27.5 deg for the hour hand.

    I2: = ((G2-INT(G2))*24-INT((G2-INT(G2))*24))*360

    or 330.0 deg for the minute hand. And

    J2: = =IF(ABS(H2-I2)<=180,ABS(H2-I2),360-ABS(H2-I2))

    57.5 deg for the separation.

    …mrt

  7. Using the standard equation (posted all over the web) for calculating the angle between the hour and minute hands, converting it to an Excel formula and performing some mathematical manipulations to force the formula to always produce the smaller angle, here is the formula I came up with…

    =ABS(30*MOD(HOUR(A1),12)-5.5*MINUTE(A1)-360*(ABS(MOD(HOUR(A1),12)-11*MINUTE(A1)/60)>12))

    This formula assumes the clock moves between minutes in one-minute jumps (that is, I do not assume the hands to creep along as the seconds pass).

  8. ***CORRECTION***

    Sorry, I grabbed the wrong formula from my test sheet. This is the correct formula to use…

    =ABS(ABS(30*MOD(HOUR(A1),12)-5.5*MINUTE(A1))-360*(ABS(ABS(30*MOD(HOUR(A1),12)-5.5*MINUTE(A1))>180)))

  9. And my UDF…

    Function TimeAngle(TimeIn As Date) As Double
    TimeAngle = Abs(30 * (Hour(TimeIn) Mod 12) – 5.5 * Minute(TimeIn))
    If TimeAngle > 180 Then TimeAngle = 360 – TimeAngle
    End Function

  10. Rick – your UDF only works for full minutes, because of the VBA mod function problem I mentioned. VBA mod converts all numbers to integers before it does its work.

  11. @Doug,

    I know it only works for whole minutes… that was the assumption I placed on my results in my first post when I said…

    “This formula assumes the clock moves between minutes in one-minute jumps (that is, I do not assume the hands to creep along as the seconds pass).”

    All I was doing with my UDF was duplicating my previously posted formula result in VB code (notice my UDF parses the TimeIn value into Hours and Minutes). I now see you did not place that restriction on your UDF; however, as written, your UDF does not always return the smaller angle between the hands; modifying it like this does…

    Function Timeangle(TimeNum As Double) As Double
      Timeangle = Abs((TimeNum * 2 – Int(TimeNum * 2)) – (TimeNum * 24 – Int(TimeNum * 24))) * 360
      If TimeAngle > 180 Then TimeAngle = 360 – TimeAngle
    End Function

  12. @Doug,

    I also meant to mention that I am well aware of VB’s rounding issues and not only as it applies to the Mod operator. Here is a link (which I think will take you directly) to a previous posting in this same blog site back about 3/4 of a year ago…

    http://www.dailydoseofexcel.com/archives/2010/07/01/vbarounding/#comment-47548

    I think the issue I raise about the Integer Division operator is even more insidious than the problem you pointed out about the Mod operator. Actually, I don’t consider the VB Mod operator to be deficient in anyway. Well, yes, compared to the Excel MOD function it is, but that is because Microsoft (or was it Lotus?) chose to extend the number space it could be applied to when it created the worksheet function. Excel’s MOD function is much newer than VB’s Mod operator. Here is how I wrote about this back in 2005 (if you read my quotes where I say “I can think of no practical use in applying the MOD operator to floating point numbers”, just remember that I wrote that before I became involved with Excel[grin])…

    http://www.ms-news.net/f3259/how-to-find-178-90-mod-89-45-in-vbulletin-vbulletin-is-returning-1-instead-of-0-a-2823681.html#post9113540

    Sorry about the missing piece that only registered members of the forum can see (I am not a registered member and their system would not let me register as one either), so I don’t know exactly what I had put there, but I think might have been a quote regarding the mathematical definition behind this functionality. This Wikipedia link should probably say the same thing…

    http://en.wikipedia.org/wiki/Modular_arithmetic

    Note the use of the word “integers” in the opening sentence.

  13. Rick – thanks for the background. I will be sure to blame Euler and not Microsoft in future :).

  14. It seems to me that everyone is making this more complicated than it has to be. Is it necessary to have to use the mod function? What’s wrong with the formula I posted earlier?

    =ABS(HOUR(G2)*DegreesPerHour-MINUTE(G2)*DegreesPerMinute)

    Where
    DegreesPerHour = 360/12 = 30
    and
    DegreesPerMinute = 360/60 = 6.

  15. @David Landry,

    Your formula is not correct. See this Wikipedia article for the relationship to use in developing an Excel formula…

    http://en.wikipedia.org/wiki/Clock_angle_problem

    The problem with the formula that results from the Wikipedia equation is that it does not alway return the smallest angle between the clock hands (and it does not account for any seconds in the time value; hence the extra complications in the postings so far.

  16. =MOD(22*G2,1)*360

    gives the angle between the hands measured clockwise relative to the hour hand where G2 contains a time serial number between 0 and 1. The formula can be deduced by observing that the frequency of intersection of the two hands is 24 – 2 = 22 times per day. To return the smaller of the clockwise and counterclockwise angles, wrap the formula above in 180-ABS(180-…).

    Most of the suggested solutions fall short (including Wikipedia but not Michael and Rick barring the minute limitation) since the direction of angle measurement changes when the minute hand passes the 12 o’clock position, for example in going from just before to just after 1pm the angle jumps by 300 degrees.

  17. David Landry
    Please try for 3:15, by your formula, it will give you zero and it should be 7.5

  18. O’clock time used as a direction is just the time proportioned to a 12-hour analog clock face. So how about

    ° = (Excel o’clock time, converted to minutes) * 360

    Excel stores time as decimal days, so

    Excel Time in Minutes = Excel Time Value * 24 * 60.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.