formula to add dates.
 

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

formula to add dates.
 

=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)

"S S" wrote in message
o.uk...
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

formula to add dates.
 

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" wrote:

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

formula to add dates.
 

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


"Ron Coderre" wrote in message
...
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" wrote:

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

formula to add dates.
 

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" wrote:

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


"Ron Coderre" wrote in message
...
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" wrote:

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

formula to add dates.
 

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=529734

formula to add dates.
 

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

A3 1-14 (this represents dates 1st to 14th)
A5 16-30 (this represents dates 16th to 30th)
A7 2-9 (this represents dates 2nd to 9th)

A3 is 14 days, A5 is 14 days, A7 is 7 days.

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


"daddylonglegs" wrote:


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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=529734

formula to add dates.
 

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.


"Ron Coderre" wrote in message
...
I thought of the other alternatives, too, but I was just trying to meet the
OP's requirements.

A3 1-14 (this represents dates 1st to 14th)
A5 16-30 (this represents dates 16th to 30th)
A7 2-9 (this represents dates 2nd to 9th)

A3 is 14 days, A5 is 14 days, A7 is 7 days.

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


"daddylonglegs" wrote:


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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread:
http://www.excelforum.com/showthread...hreadid=529734

formula to add dates.
 

Yes the formula is working perfect for me.

Thanks to all for their help.

appreciated.


"S S" wrote in message
o.uk...
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.


"Ron Coderre" wrote in message
...
I thought of the other alternatives, too, but I was just trying to meet
the
OP's requirements.

A3 1-14 (this represents dates 1st to 14th)
A5 16-30 (this represents dates 16th to 30th)
A7 2-9 (this represents dates 2nd to 9th)

A3 is 14 days, A5 is 14 days, A7 is 7 days.

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


"daddylonglegs" wrote:


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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread:
http://www.excelforum.com/showthread...hreadid=529734