Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    time formatting and adding (2000 sr1)

    I need to find a way to have excel add times together. Here is what I am doing:
    My template has three collumns.
    In Cell C1, one enters a start time, such as 5:00
    collumn A is where one would enter a time duration, such as 50, for 50 minutes
    Collumn B is where one enters text of the event
    collumn C adds the starting time to the event duration to get a new time, in this case, 5:50.

    I do not need to differentiate between AM and PM
    I would prefer to only have to enter times as simple numbers, such as 50 for :50, and 500 for 5:00.

    Any help? I don't think its too hard, but it has me confused.

    Rick

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: time formatting and adding (2000 sr1)

    John,
    thanks for the help. The minutes cell that I enter in only displays "0:00". It is adding correctly, but each time I enter in the minutes, i.e. "50", it displays 0:00. Is there a way to correct this?

    I know it works, but why do I divide by a negative 1440?
    Rick

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

    Re: time formatting and adding (2000 sr1)

    You should be dividing by positive 1440, not negative. That is because Excel keeps time as the fractional part of a day and there are 1440 minutes in 24 hours (24*60).

    However, I do not see how you could be getting 0:00. Could you tell us exactly what you have in that cell and how you have it formatted?
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: time formatting and adding (2000 sr1)

    If you ignore AM and PM, you'll need to use 24 hour time, not 12 hour time. Trick is to remember that Excel measures time in fractions of a day. Titles start in A1, format cells A2 and C2 as h:mm Time, format cell B2 as number or general, minutes must be entered as a number, try this:

    Start Time Minutes End Time
    5:00 50 =A2+B2/1440
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: time formatting and adding (2000 sr1)

    Format the minutes cell as general or number.

    1440 minutes in a day; will convert Excel's fractions of a day to whole minutes. Should not be a negative number, I had originally written it as (24*60), but the parens were never necessary.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: time formatting and adding (2000 sr1)

    I just figured out how you might be getting 0:00. If you format the cell containing 50 as a time, that will give you 0:00 because the 50 is not an Excel date/time serial number. If you format that cell as a date and time, you will see that Excel is interpreting it as midnight on February 19, 1900, which is exactly 50 days from midnight on January 1, 1900. You would have to divide the 50 by 1440 to convert it to a Date/Time Serial Number to format the cell as time. Format the cell as General or as a number.
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: time formatting and adding (2000 sr1)

    Thanks
    It all makes sense to me now!!!!

    Rick

Posting Permissions

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