Formula to calculate time

[Guest]

I was wondering if anybody knows of a formula to allow you to do calculations
based on time. In other words, say I want to put a list of songs on a CD and
their durations. Then, I want to add up the durations to get the full time of
the CD, or my specific play list. So for example, using the information below:

Ghostbusters - Ray Parker Jr. --- 5:23 (Just a guess)
Phantom of the Opera Overture --- 3:55 (Another guess)

Instead of 8:78, I'd want to see 9:18

Can this be done? Any help would be greatly appreciated. Thank you!

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

 

[Guest]

Instead of 5:23 and 3:55

write 00:05:23 and the same for 00:03:55

HTH

 

[Voodoodan]

Unfortunately, the following is only helpful if you don't mind putting
the hours and minutes in separate cells.

If this is ok then you could use these formulas to total:

HOURS
=INT((SUM(-[range of hour column]-)*60+SUM(-[range of minute
column]-))/60)


MINUTES
=SUM(-[range of hour column]-)*60+SUM(-[range of minute
column]-)-(-[cell where hours formula, shown above, is located]-*60)

Dan

 

[Guest]

Awesome! Thanks, that works. You know something? I feel silly now, though. I
didn't even bother to try first, I just assumed it would need some kind of
formula to understand time. I found out, though, if I just type it in some
kind of time format, Excell calculates it as time. It even worked when I just
typed in 5:23 and 3:55.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

 

[Guest]

Oops. Nevermind, I spoke to soon. Unless I enter it the way you said, it
takes it as hours and minutes instead of minutes and seconds. So, I have to
enter it the way you said or it won't work.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

 

[Ron Rosenfeld]

I was wondering if anybody knows of a formula to allow you to do calculations
based on time. In other words, say I want to put a list of songs on a CD and
their durations. Then, I want to add up the durations to get the full time of
the CD, or my specific play list. So for example, using the information below:

Ghostbusters - Ray Parker Jr. --- 5:23 (Just a guess)
Phantom of the Opera Overture --- 3:55 (Another guess)

Instead of 8:78, I'd want to see 9:18

Can this be done? Any help would be greatly appreciated. Thank you!

Not sure what your problem is. Did you try it?

If I put

A1: 5:23
A2: 3:55
A3: =A1+A2 =9:18

Of course, Excel is interpreting this as 9 hrs 18 min and not 9min 18sec; but
all you have to do is divide by 60, it it's important.

In the first case, format the result as [h]:mm
If you divide by 60, format as either [m]:ss or [h]:mm:ss depending on how you
want your output.


--ron

 

[Guest]

Of course, Excel is interpreting this as 9 hrs 18 min and not 9min 18sec; but
all you have to do is divide by 60, it it's important.

Yeah, it worked okay except for that. All I had to do was enter in the full
time (including 0 hours) the way traineeross said and it worked fine. Thanks
for your suggestion as well, Voodoodan. That actually probably will come in
handy for a future project, where it will be more beneficial for me to use
your suggestion instead.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

Unfortunately, the following is only helpful if you don't mind putting
the hours and minutes in separate cells.

If this is ok then you could use these formulas to total:

HOURS
=INT((SUM(-[range of hour column]-)*60+SUM(-[range of minute
column]-))/60)


MINUTES
=SUM(-[range of hour column]-)*60+SUM(-[range of minute
column]-)-(-[cell where hours formula, shown above, is located]-*60)

Dan