formula to add dates.

  • Thread starter Thread starter S S
  • Start date Start date
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
 
=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)
 
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
 
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...
 
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...
 
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
 
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
 
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.
 

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
Finding last cell with a value and then adding to another 2
Workday Formula to include date 0
Coppy formula using OFFSET 4
No of Days between two dates and offset formula 4
COUNT function 2
Return next non-Blank Cell in a Column ? 4
help with formula for last input in column 6

Back
Top