Results 1 to 7 of 7

20020326, 16:35 #1
 Join Date
 Jan 2001
 Location
 Columbus, Ohio
 Posts
 254
 Thanks
 7
 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

20020326, 20:52 #2
 Join Date
 Jan 2001
 Location
 Columbus, Ohio
 Posts
 254
 Thanks
 7
 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

20020326, 21:00 #3
 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

20020326, 21:03 #4
 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/1440John ... I float in liquid gardens
UTC 7ąDS

20020326, 21:04 #5
 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

20020326, 21:07 #6
 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

20020326, 21:22 #7
 Join Date
 Jan 2001
 Location
 Columbus, Ohio
 Posts
 254
 Thanks
 7
 Thanked 0 Times in 0 Posts
Re: time formatting and adding (2000 sr1)
Thanks
It all makes sense to me now!!!!
Rick