Summing DATEDIF Column

[jimbob999]

Hi,

I can't seem to find a solution for Summing a column with a DATEDIF forumla in. The number of rows can reach over a 100. For example:

Column A and B are dates entred for this year.

06-Jan 28-Jan 23 days
07-Jan 05-Apr 89 days
15-Jan 28-Jan 14 days
17-Jan 05-Feb 20 days

In Column C
=IF((ISBLANK(A1)),"",IF(ISBLANK(B1),(DATEDIF(A1,TODAY()+1,"d")&" days "),(DATEDIF(A1, B1+1,"d"))&" days "))

So I am looking to sum up column C.

Any ideas?

Thanks,

[BadlySpelledBuoy]

Depending on which version of Excel you're actually using (don't know if your profile is up to date or not) you could try either of the below:
=SUM(SUBSTITUTE(C1:C4," days","")*1)
=SUMPRODUCT(SUBSTITUTE(C1:C4," days","")*1)

BSB

[JohnTopley]

Simply remove the "days" text from the formula and then SUM.

[davsth]

=SUMPRODUCT((A1:A4>0)*(B1:B4>0)*(B1:B4-A1:A4+1))+SUMPRODUCT((A1:A4>0)*(B1:B4="")*(TODAY()-A1:A4+1)) although their may be a more succinct answer

or
=IF((ISBLANK(A1)),"",IF(ISBLANK(B1),today()+1 -a1,b1-A1+1))

format cells as custom format 0 \d\a\y\s

then you can sim these with a normal sum function

[jimbob999]

Thanks for the reply guys but I am having a bit of trouble still.

1. The rows can go to infinity, and are always being input, is there a way to add to SUM the whole C column for when extra data is input, without having to change the formula every time?

2. The first row is actually a title? so row A is text, does will this effect how to sum column C?

johntropley - This solution didnt seem to work for me? Not sure why or if I did it wrong. It kept coming back with a value of 0.


BSB =SUM(SUBSTITUTE(C1:C4," days","")*1) This one seemed to work the best, however I wasn't able to get it to work for the whole of the C coloumn, as in for soon as it goes past a cell without any data, if just errors. Currently I'm down to row 55 so (C2:C55), but soon as I hit (C2:C56) which is a blank cell, comes up with VALUE error. Is there a way so this works to infinity?

Thanks again for your helps guys!

[BadlySpelledBuoy]

You could use a "dynamic named range" that will expand/contract as you add/remove data.

Press Ctrl+F3 to open the Name Manager window and create a new named range called "DaysTotal".
In the formula section of the window enter the below:
=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C),1)

Then change the formula to this:
=SUM(SUBSTITUTE(DaysTotal," days","")*1)

Change your original formula to:
=IF((ISBLANK(A1)),0,IF(ISBLANK(B1),(DATEDIF(A1,TODAY()+1,"d")&" days "),(DATEDIF(A1, B1+1,"d"))&" days "))

Change the custom formatting of column C to #"" (this will leave the cells with 0 looking blank/empty but stops the formula falling over)

Now when you add remove data the formula will only be looking at the range data is entered into.

See attached.

All seems like a lot of effort when Mr Topley's reply give a nice simple solution.

BSB

[JohnTopley]

See attached

=IF((ISBLANK(A2)),"",IF(ISBLANK(B2),(DATEDIF(A2,TODAY()+1,"d")),(DATEDIF(A2, B2+1,"d"))))

[jimbob999]

Perfect!!!

Thanks guys, managed to go for the simple answer from John Topley and get it to work.

Not sure why I couldn't work it out for myself as it seems so simple. Maybe I had been staring at it for too long.

and thanks BSB, I might try looking at creating a dynamic named range eventually, its something I havent done before, but think I might try out when I get a bit of spare time

[JohnTopley]

See attached which adds formula to column C starting row 2

Please Login or Register  to view this content.

sum in F2