Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Add Time Together (Excel 2000)

    I am an Instructional Designer and during the development of the course ware that I create, I have an agenda of start times and duration times so the facilitator will know what time a particular module needs to start and how many minutes the module should last.

    For Example:

    A B C
    1 Topic Estimated Time Start Time
    2 Day Start 10 Min. 8:00
    3 Start your PC 15 Min. 8:10
    4 Start Excel 5 Min. 8:25
    5 Format A Cell 5 Min. 8:30
    6 Etc. 15 Min. 8:35

    What I am looking for is a formula that will allow me to add a specific number of minutes listed in one cell to a time listed in another cell and determine what the new time is.

    I am not the greatest spread sheet user so more detail on how to do things will be very helpful. Of if you know of a book with this information in it that you can reference me to that would be great.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to Add Time Together (Excel 2000)

    You can use +Time(h,m,s) to add hours, minutes or seconds to a given time. To add 15 minutes to a time value in A1 you would use <pre> =A1+Time(0,15,0)</pre>

    or<pre> =A1+Time(1,10,20) </pre>

    would add 1 hour, 10 minutes and 20 seconds

    You just need to be sure that the value in A1 is a time value (or date and time)

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

    Re: How to Add Time Together (Excel 2000)

    From your example, it looks like you would have the duration in B2 and the Starting time in C2. In addition, it looks like the duration is not a number but a text value that includes " Min" after the number. That makes it a little more difficult since you have to remove the " Min" and convert what is left to a number, then convert that to a time value before you can add it to the starting time. The following formula does all of that:

    <pre>=C2+TIME(0,VALUE(TRIM(LEFT(TRIM(B2),SEARCH( " ",TRIM(B2))))),0)
    </pre>


    If B2 contained just the duration, then that formula can be simplified to:

    <pre>=C2+TIME(0,B2,0)
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Add Time Together (Excel 2000)

    The previous two replies are undoubtedly the "proper" way to do it, but if you have a number of modules, evaluating the time function could take time. There is a "dirty" way to do it, though - Excel stores days as whole numbers and hours, minutes, seconds as fractions of one. As one minute represents one 60th of one 24th of a whole day, you could just divide the minutes duration by 60*24, or 1440.

    i.e. start time for module = start time for previous module + (duration of previous module [in minutes] / 1440)

    Regards,

    Stuart

  5. #5
    alterego
    Guest

    Re: How to Add Time Together (Excel 2000)

    That's how I'd do it too. It might just be worth mentioning that the time entries have to be in time formatted cells. If you divide the result (which should be a normal numeric format) by 24, you get a sometimes useful conversion to decimal hours, eg. 14.25 hours

Posting Permissions

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