Results 1 to 3 of 3
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Time Arithmetic (2003)

    I've got a table of sunrise and sunset times in text format, and I want to display the length of the period of daylight.

    The sunrise and sunset appear in columns, formatted for example 0743 1704. This means that the sun rises at 07:43 local time on this day, and sets at 17:04. The length of this day is 9 hours, 21 minutes, which I would like to display as 09:21 or 9:21.

    I've got things working fairly well by using VALUE, LEFT and RIGHT on the 0743, etc. to calculate the number of minutes in the day. Then I use INT and MOD to calculate the number of hours and minutes from the number of minutes.

    I can't figure how to get the desired 9:21 or 09:21 display, though, because leading zeroes get dropped. That's acceptable for the hours, but not for the minutes. I can live with 9:21, but not with 10:0 (for ten hours, zero minutes). Can anybody help?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Arithmetic (2003)

    If that string is in cell A1, then the following formula will calculate the difference as an Excel time value. You can then formate the cell containing the formula as Time.

    <code>
    =VALUE(MID(A1,6,2)&":"&RIGHT(A1,2)&":00")-VALUE(LEFT(A1,2)&":"&MID(A1,3,2)&":00")
    </code>
    Legare Coleman

  3. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Time Arithmetic (2003)

    Got it! Thanks.

    It's simpler than I thought it would be, and I've got it working now with 07:43 and 17:34 (including the colons) in separate columns, which is my preferred way of displaying sunrise and sunset. Couldn't have done it without you. (Or maybe I could have gotten it, after many hours of experimentation!)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •