| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP & Dates: Why is this Formula working? | Excel Worksheet Functions | |||
| date formula for extracting unique dates | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula including dates | Excel Worksheet Functions | |||
| How do I format dates accessed by a formula | Excel Discussion (Misc queries) | |||
Hybrid Mode
