adding up hours and minutes

  • Thread starter Thread starter Marie Lavoie
  • Start date Start date
M

Marie Lavoie

Hi.
I have 2 questions.

1- How can I format a cell so it shows the number of hours and minutes in
the same format as time. (35:20) (right now, it turns it back to an hour
between 0 and 24 or it takes it as text)
2- How can I add up many of those cells. (So that 0:30+0:30 = 1:00 for
example)

Thank you.

Marie
 
Format > Custom > Type: [h]:mm
(this will allow total time > 24 hours to display)

Just use normal sum to total up the time, e.g.: =SUM(A1:A2)
 
When I do that, It converts what I write...
(Like the dates, wich I hate. You have to write in the default format, not
the format in the cells).
What to do?
Also, My values are already all written down (I copied a table that was in
wordperfect before, so it can sum by it's own)
Thank you.

Marie

Max said:
Format > Custom > Type: [h]:mm
(this will allow total time > 24 hours to display)

Just use normal sum to total up the time, e.g.: =SUM(A1:A2)

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
Marie Lavoie said:
Hi.
I have 2 questions.

1- How can I format a cell so it shows the number of hours and minutes in
the same format as time. (35:20) (right now, it turns it back to an hour
between 0 and 24 or it takes it as text)
2- How can I add up many of those cells. (So that 0:30+0:30 = 1:00 for
example)

Thank you.

Marie
Click to expand...
Click to expand...
 
Are you trying to add the date/time stamps (ie mm/dd/yy hh:mm) or the result
of subtracting one date from another? I have a report that shows total
travel time and I first subtract the two times and then add up the results.
Max's suggestion works perfectly in my report.

(BTW, thanks Max, I needed the formatting tip).

Brian

Marie Lavoie said:
When I do that, It converts what I write...
(Like the dates, wich I hate. You have to write in the default format, not
the format in the cells).
What to do?
Also, My values are already all written down (I copied a table that was in
wordperfect before, so it can sum by it's own)
Thank you.

Marie

Max said:
Format > Custom > Type: [h]:mm
(this will allow total time > 24 hours to display)

Just use normal sum to total up the time, e.g.: =SUM(A1:A2)

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
Marie Lavoie said:
Hi.
I have 2 questions.

1- How can I format a cell so it shows the number of hours and minutes in
the same format as time. (35:20) (right now, it turns it back to an hour
between 0 and 24 or it takes it as text)
2- How can I add up many of those cells. (So that 0:30+0:30 = 1:00 for
example)

Thank you.

Marie
Click to expand...
Click to expand...
Click to expand...
 
Are you saying that when you make a new entry in that range (column?), then that
new entry has the format you want--but the cells with existing values didn't
change format?

If that's correct, try this on one of the troublesome cells.
Select that cell
make sure it's formatted as: [h]:mm
Hit F2
Hit enter
Did the format change?

If yes, then excel isn't seeing your data as a date--maybe just text.

Try selecting that range,
make sure it's formatted correctly [h]:mm
and then
edit|Replace
what: : (just a colon)
with: : (the same colon)
Replace all.

Excel may see that change and reevaluate your value and see it as time.

If that didn't work, then check to see if you have any leading/trailing spaces
in your cell. Maybe getting rid of them would help. And David McRitchie has a
macro solution that you may want to use:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Marie said:
When I do that, It converts what I write...
(Like the dates, wich I hate. You have to write in the default format, not
the format in the cells).
What to do?
Also, My values are already all written down (I copied a table that was in
wordperfect before, so it can sum by it's own)
Thank you.

Marie

Max said:
Format > Custom > Type: [h]:mm
(this will allow total time > 24 hours to display)

Just use normal sum to total up the time, e.g.: =SUM(A1:A2)

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
Marie Lavoie said:
Hi.
I have 2 questions.

1- How can I format a cell so it shows the number of hours and minutes in
the same format as time. (35:20) (right now, it turns it back to an hour
between 0 and 24 or it takes it as text)
2- How can I add up many of those cells. (So that 0:30+0:30 = 1:00 for
example)

Thank you.

Marie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top