Excel 3 colums in table Start, End, Duration

Greetings,
I am having a strange boggle with linking time between Start, End, Duration for each line in my table; first, here is the sample of what I have thus far:

C5=Start Time (lookup to D4)
D5=End Time (calculation by adding value in C5 to duration in E5)

=C5+TIME(E5,0,0)

However, when I want to show duration including minutes, instead of whole hours, I can't seem to get the display correct...it won't who minutes
eg. if the Duration=1 hour 10 minutes,  how do I express that numerically in E5 so it is understood by the formula above?

Thank you, in-advance,
JohnD
LVL 1
John DarbyPMAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
If you want to show total minutes, use the custom number format:

    [m]

Kevin
0
Danny ChildIT ManagerCommented:
if you can post an example sheet, that's really helpful.
0
zorvek (Kevin Jones)ConsultantCommented:
I re-read your question and I have a better answer.

When you use E5 in the TIME formula the way you are, you are assuming that E5 is in hours - this means the integer or part to the left of the decimal is the number of hours. The TIME function does not consider a fraction as a fraction of an hour - it takes only the integer portion of the parameter. So TIME(12.5,0,0) equates to 12 hours, not 12 hours and 30 minutes. To add 12 hours and 30 minutes you have to use TIME(12,30,0),

A very nice feature of Excel is that it stores time values as a fraction of a day. So 12 PM is stored internally as 0.5. 6 PM is stored as 0.75. This is very useful in that a date/time value can be stored as one number where the integer portion of the value is the day (number of days since January 1, 1900) and the fractional portion of the number is the time on that day.

Knowing this reveals an interesting fact: we can do math with dates and times without using the TIME and DATE functions. For example, if you enter the value in E5 (the duration) as a real date/time value, then your formula in D5 becomes:

    C5+E5

To use your example, the value in E5 of 1 hour and 10 minutes is entered as 1:10 or 1:10:00 (stored internally as 0.0486111). When added the value in C5 you will get the date/time in C5 plus 1 hour and 10 minutes.

If you want to display the value in E5 as hours and minutes you can format it as:

    h:mm

which displays "1:10"

or

    h "hours and" m "minutes"

which displays "1 hours and 10 minutes"

or

    [m]

which displays "70".

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
The multiplier/divisor for minutes is 1440
0
John DarbyPMAuthor Commented:
Thank you so much! The hh:mm:ss helped immensely!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.