formula to add dates.

S

S S

Looking for a formula to add the figures below.



Cells are in column A row 3 to 50 Example.



A3 1-14 (this represents dates 1st to 14th)

A4 (blank ,no dates)

A5 16-30 (this represents dates 16th to 30th)

A5 (blank,no dates)

A6 (blank,no dates)

A7 2-9 (this represents dates 2nd to 9th)



And so on until row 50

I am looking for a formula that will work out that A3 is 14 days, A5 is 14
days, A7 is 7 days. And add them to give a total of 35 days in the cell I
put the formula. The dates will obviously vary from cell to cell and some
will be blank.



thanks
 
B

Bob Phillips

=SUM(IF(LEN(A1:A50)<>(LEN(SUBSTITUTE(A1:A50,"-",""))),--MID(A1:A50,FIND("-",
A1:A50)+1,99)-LEFT(A1:A50,FIND("-",A1:A50)-1)+1))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
G

Guest

See if this array formula works for you:

A51:
=SUMPRODUCT(IF(ISNUMBER(SEARCH("-",A3:A50)),--MID(A3:A50,SEARCH("-",A3:A50)+1,255))-IF(ISNUMBER(SEARCH("-",A3:A50)),--LEFT(A3:A50,SEARCH("-",A3:A50)-1)))+COUNTIF(A3:A50,"1-*")

Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
S

S S

I think I have copied and entered ok but it is returning an answer of 1


Ron Coderre said:
See if this array formula works for you:

A51:
=SUMPRODUCT(IF(ISNUMBER(SEARCH("-",A3:A50)),--MID(A3:A50,SEARCH("-",A3:A50)+1,255))-IF(ISNUMBER(SEARCH("-",A3:A50)),--LEFT(A3:A50,SEARCH("-",A3:A50)-1)))+COUNTIF(A3:A50,"1-*")

Note: To commit array formulas, hold down [Ctrl] and [Shift] when you
press
[Enter].

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


S S said:
Looking for a formula to add the figures below.



Cells are in column A row 3 to 50 Example.



A3 1-14 (this represents dates 1st to 14th)

A4 (blank ,no dates)

A5 16-30 (this represents dates 16th to 30th)

A5 (blank,no dates)

A6 (blank,no dates)

A7 2-9 (this represents dates 2nd to 9th)



And so on until row 50

I am looking for a formula that will work out that A3 is 14 days, A5 is
14
days, A7 is 7 days. And add them to give a total of 35 days in the cell
I
put the formula. The dates will obviously vary from cell to cell and some
will be blank.



thanks
Click to expand...
Click to expand...
 
G

Guest

In case window wrap impacted the display, there are no spaces in the formula
I posted. You can probably just copy it right from the window into Excel and
delete any carriage returns that break up the formula.

=SUMPRODUCT(IF(ISNUMBER(SEARCH("-",A3:A50)),--MID(A3:A50,SEARCH("-",A3:A50)+1,255))-IF(ISNUMBER(SEARCH("-",A3:A50)),--LEFT(A3:A50,SEARCH("-",A3:A50)-1)))+COUNTIF(A3:A50,"1-*")

(Don't forget to Ctrl+Shift+Enter)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


S S said:
I think I have copied and entered ok but it is returning an answer of 1


Ron Coderre said:
See if this array formula works for you:

A51:
=SUMPRODUCT(IF(ISNUMBER(SEARCH("-",A3:A50)),--MID(A3:A50,SEARCH("-",A3:A50)+1,255))-IF(ISNUMBER(SEARCH("-",A3:A50)),--LEFT(A3:A50,SEARCH("-",A3:A50)-1)))+COUNTIF(A3:A50,"1-*")

Note: To commit array formulas, hold down [Ctrl] and [Shift] when you
press
[Enter].

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


S S said:
Looking for a formula to add the figures below.



Cells are in column A row 3 to 50 Example.



A3 1-14 (this represents dates 1st to 14th)

A4 (blank ,no dates)

A5 16-30 (this represents dates 16th to 30th)

A5 (blank,no dates)

A6 (blank,no dates)

A7 2-9 (this represents dates 2nd to 9th)



And so on until row 50

I am looking for a formula that will work out that A3 is 14 days, A5 is
14
days, A7 is 7 days. And add them to give a total of 35 days in the cell
I
put the formula. The dates will obviously vary from cell to cell and some
will be blank.



thanks
Click to expand...
Click to expand...
Click to expand...
 
D

daddylonglegs

Shouldn't the answer be 37?

Perhaps

=SUM(IF(ISERR(FIND("-",A3:A50)),,REPLACE(A3:A50,1,FIND("-",A3:A50),"")-LEFT(A3:A50,FIND("-",A3:A50)-1)+1))

confirmed with CTRL+SHIFT+ENTER
 
G

Guest

I thought of the other alternatives, too, but I was just trying to meet the
OP's requirements.

I inferred from the post that the 1st of the month IS counted if it begins a
range, otherwise the start day of the range is NOT counted.

So...until we hear otherwise...?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
S

S S

Ok I think its now working for me, It has returned a higher answer but I
need to dash off for work and will double check it tonight.
I will confirm tonight if all is ok.

thanks for your help.
 
S

S S

Yes the formula is working perfect for me.

Thanks to all for their help.

appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Comparison of columns using Conditional Formatting 3
Excel Dates 3
Finding last cell with a value and then adding to another 2
Coppy formula using OFFSET 4
Excel Is this possible?? 3
help with datedif function please 4
COUNT function 2
Compound interest 1

Top