Adding days.

[GEM]

I have dates on two columns and they are formated to be dates...

A1=2/10/2009 2:55:00 PM
B1=2/14/2009 10:51:00 AM
C1=INT(B1-A1)&" days "&TEXT(B1-A1,"hh:mm")

C1 gives me the exact days and hours between B1 and A1 (3 days 19:56),
column C is also formatted to date. Now I want to add all the results from
column C, so I used a simple =SUM(C1:C4) and I get #VALUE!

How can I add all the results from column C???

 

[Niek Otten]

This makes C1 text:

&" days "&TEXT(B1-A1,"hh:mm")

You can't add text

Use separate cells for calculation and display

 

[GEM]

What function can I use to get an answer on C3 as "x days x time" and still
be able to add those numbers??

 

[Niek Otten]

You can't. Use separate cells for adding. You can hide them.

 

[JBeaucaire]

Pete UK gave you the solution you tried, but you implemented it incorrectly:

You need to just out the formula =B1-A1 in cell C1, then press CTRL-1 to
open the cell format box. Select CUSTOM format and enter the string Pete gave
you as the custom format:

d" days "hh:mm

Exactly like that. Now the cell will LOOK like "1 days 23:11" but it it's
still really just a number, and that means you can do further math on it.