Format the sum cell

[Guest]

hello,

how can i format the cell to display the sum of some time cells like

input
10:00
30:00
20:00

Sum
60:00 <- Problem!!!! it displays only 12:00

i want something like 02:12:00 (2 days 12 hours 00 Min)

thanks

i tryed to use something - hh:mm but it displays

 

[Norman Jones]

Hi Xavier,

Try using the custom format:

[h]:mm

 

[Bob Phillips]

Format as [h]:mm which will cater for more than 24 hours.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

formatting with time 37:30:55 will display the corect values in hours but
not the values in a format like:
day hours:minutes...

regards

 

[Roger Govier]

Hi Xavier
Format the cell with the summation formula as dd:hh:mm

 

[Norman Jones]

Hi Xavier,

formatting with time 37:30:55 will display the corect values in hours
but
not the values in a format like:
day hours:minutes...

Try the custom format:

dd hh:mm

 

[Guest]

Format as d hh:mm

 

[Guest]

i tryed something like

=TEXT(SUM(F39);"tt:hh:mm")

where SUM(F39) is 28:00 (28 hours) ..

but now i get 02:04:00 (wy 02 and not 01?)

thanks

 

[Guest]

i tryed with

=TEXT(SUM(F39);"tt:hh:mm")
or
=TEXT(SUM(F39);"tt hh:mm")
because it is a german version instead of dd .....

but it displays a wrong value

for 28 hours it displays 02 04:00

any ideas?
thanks

 

[Norman Jones]

Hi Xavier,

Why not simply select the sum cell and custom format it as suggested?

Format | Cells | Custom | dd hh:mm

Obviously, you will need to adjust this to accord with your German version
of Excel.

Incidentally, using your Text function worked for me with the indicated
data.

 

[Guest]

i did a mistake ... all your sugesstions works

thanks a lot for your quick help

regards

 

[Guest]

hello Norman
you have right (i did not know this ....) ... but:

i use a 1904 Date System ... becaus i have to display also negative minutes
....

If i format the sum of times with a value for example 17:00 ( 17 hours)
and i change the format to d hh:mm it displays

1 17:30 and not 0 17:30

any ideas ...

 

[Guest]

formatting with time 37:30:55 will display the corect values in hours but
not the values in a format like:
day hours:minutes...

regards