Adding days.

  • Thread starter Thread starter GEM
  • Start date Start date
G

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???
 
This makes C1 text:

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

You can't add text

Use separate cells for calculation and display
 
What function can I use to get an answer on C3 as "x days x time" and still
be able to add those numbers??
 
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.
 
Back
Top