Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Different Columns of Dates
I'm thinking this must be impossible becasue I haven't received any response. If there is anybody pretty knowlegeable and they know it is impossible or think it is would you please tell me and I will stop driving myself crazy trying to figure it out
1st Column 2nd column 3rd Column 3/31/04 $500.00 2/1/02 2/01/01 600.00 1/15/04 I want to pick up any dollar amount that falls within the 1qt 04, whether it is the first or third column. So I should pick up $1,100.00. I had a code that picked up the first column but of course somebody always has to change something, and now they tell me if the 3rd column has a 1qt 04 date they want it also. This is the code I am trying to use. If anybody would know of another site that would explain this or a book that I could read about this or ANYTHING, I would be forever greatful!!! Please Application.Worksheets("Dates").Columns("g").Numbe rFormat = "mm/dd/yy" Application.Worksheets("Dates").Columns("l").Numbe rFormat = "mm/dd/yy" For Each Cell In Range("NoteList") Cell.Offset(0, 5).Value = Cell.Offset(0, 5).Value 'this being the first date Cell.Offset(0, 10).Value = Cell.Offset(0, 10).Value 'this being the second date If Cell.Offset(0, 5) Range("HistDate") And Cell.Offset(0, 5) <= Range("controldate") Then If Cell.Offset(0, 10) Range("HistDate") And Cell.Offset(0, 10) <= Range("controldate") Then Select Case Cell.Offset(0, 2) 'this being the amount |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Different Columns of Dates
Here's a non VBA way... you'll need an extra column... If your 1st Column Value is A1 and 2d column B1 an so on... On the fourth column type this formula: =IF(OR(IF(AND(A1=37987,A1<=38077),1,0)=1,IF(AND (C1=37987,C1<=38077),1,0)=1),B1,0) This will give you the dollar ammount if there is a first 1/4 of 2004 date on either the 1st or 3rd column then simply add the total of this fourth column... Regards Juan Everithing is possible... almost! -----Original Message----- I'm thinking this must be impossible becasue I haven't received any response. If there is anybody pretty knowlegeable and they know it is impossible or think it is would you please tell me and I will stop driving myself crazy trying to figure it out 1st Column 2nd column 3rd Column 3/31/04 $500.00 2/1/02 2/01/01 600.00 1/15/04 I want to pick up any dollar amount that falls within the 1qt 04, whether it is the first or third column. So I should pick up $1,100.00. I had a code that picked up the first column but of course somebody always has to change something, and now they tell me if the 3rd column has a 1qt 04 date they want it also. This is the code I am trying to use. If anybody would know of another site that would explain this or a book that I could read about this or ANYTHING, I would be forever greatful!!! Please Application.Worksheets("Dates").Columns("g").Numb erFormat = "mm/dd/yy" Application.Worksheets("Dates").Columns("l").Numb erFormat = "mm/dd/yy" For Each Cell In Range("NoteList") Cell.Offset(0, 5).Value = Cell.Offset(0, 5).Value 'this being the first date Cell.Offset(0, 10).Value = Cell.Offset(0, 10).Value 'this being the second date If Cell.Offset(0, 5) Range("HistDate") And Cell.Offset(0, 5) <= Range("controldate") Then If Cell.Offset(0, 10) Range("HistDate") And Cell.Offset(0, 10) <= Range("controldate") Then Select Case Cell.Offset(0, 2) 'this being the amount . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Different Columns of Dates
do you have to use a macro
=SUM(IF((A1:A10=DATEVALUE("1/1/04"))*(A1:A10<=DATEVALUE("3/31/04"))+(C1:C10 =DATEVALUE("1/1/04"))*(C1:C10<=DATEVALUE("3/31/04"))0,B1:B10)) Entered with Ctrl+shift+Enter should do it. -- Regards, Tom Ogilvy "Marie" wrote in message ... I'm thinking this must be impossible becasue I haven't received any response. If there is anybody pretty knowlegeable and they know it is impossible or think it is would you please tell me and I will stop driving myself crazy trying to figure it out 1st Column 2nd column 3rd Column 3/31/04 $500.00 2/1/02 2/01/01 600.00 1/15/04 I want to pick up any dollar amount that falls within the 1qt 04, whether it is the first or third column. So I should pick up $1,100.00. I had a code that picked up the first column but of course somebody always has to change something, and now they tell me if the 3rd column has a 1qt 04 date they want it also. This is the code I am trying to use. If anybody would know of another site that would explain this or a book that I could read about this or ANYTHING, I would be forever greatful!!! Please Application.Worksheets("Dates").Columns("g").Numbe rFormat = "mm/dd/yy" Application.Worksheets("Dates").Columns("l").Numbe rFormat = "mm/dd/yy" For Each Cell In Range("NoteList") Cell.Offset(0, 5).Value = Cell.Offset(0, 5).Value 'this being the first date Cell.Offset(0, 10).Value = Cell.Offset(0, 10).Value 'this being the second date If Cell.Offset(0, 5) Range("HistDate") And Cell.Offset(0, 5) <= Range("controldate") Then If Cell.Offset(0, 10) Range("HistDate") And Cell.Offset(0, 10) <= Range("controldate") Then Select Case Cell.Offset(0, 2) 'this being the amount |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Different Columns of Dates
Thanks for your help! But, I have to use a macro because there is other select criteria I have to pick up after the two amounts are pulled (that I have set up as Case 302 etc) for example this is right below the selection of the two columns:
Case 302 To 303, 400 To 403 'Company 1 ' CO200 = CO200 + cell.Offset(0, 1).Value ' If cell.Offset(0, 6) = 30 And cell.Offset(0, 9) < "X" Then ' CO200del = CO200del + cell.Offset(0, 1).Value ' End If I just need to know the line I need to add to pick up the second group before all the select criteria , or am I all wet in thinking it can be done that way? I thought if it was before the case selection it would automatically add it to - for example, CO200. "Tom Ogilvy" wrote: do you have to use a macro =SUM(IF((A1:A10=DATEVALUE("1/1/04"))*(A1:A10<=DATEVALUE("3/31/04"))+(C1:C10 =DATEVALUE("1/1/04"))*(C1:C10<=DATEVALUE("3/31/04"))0,B1:B10)) Entered with Ctrl+shift+Enter should do it. -- Regards, Tom Ogilvy "Marie" wrote in message ... I'm thinking this must be impossible becasue I haven't received any response. If there is anybody pretty knowlegeable and they know it is impossible or think it is would you please tell me and I will stop driving myself crazy trying to figure it out 1st Column 2nd column 3rd Column 3/31/04 $500.00 2/1/02 2/01/01 600.00 1/15/04 I want to pick up any dollar amount that falls within the 1qt 04, whether it is the first or third column. So I should pick up $1,100.00. I had a code that picked up the first column but of course somebody always has to change something, and now they tell me if the 3rd column has a 1qt 04 date they want it also. This is the code I am trying to use. If anybody would know of another site that would explain this or a book that I could read about this or ANYTHING, I would be forever greatful!!! Please Application.Worksheets("Dates").Columns("g").Numbe rFormat = "mm/dd/yy" Application.Worksheets("Dates").Columns("l").Numbe rFormat = "mm/dd/yy" For Each Cell In Range("NoteList") Cell.Offset(0, 5).Value = Cell.Offset(0, 5).Value 'this being the first date Cell.Offset(0, 10).Value = Cell.Offset(0, 10).Value 'this being the second date If Cell.Offset(0, 5) Range("HistDate") And Cell.Offset(0, 5) <= Range("controldate") Then If Cell.Offset(0, 10) Range("HistDate") And Cell.Offset(0, 10) <= Range("controldate") Then Select Case Cell.Offset(0, 2) 'this being the amount |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Different Columns of Dates
Dim q1LB as Date, q1UB as Date
Dim cell as Range q1LB = DateValue("1/1/2004") q1UB = DateValue("3/31/2004") for each cell in range(??) if cell.offset(0,offset to column1).Value = q1LB and _ cell.offset(0,offset to column1).Value <= q1UB _ or _ cell.offset(0,offset to column2).Value = q1LB and _ cell.offset(0,offset to column2).Value <= q1UB then select Case cell.Value Case 302 To 303, 400 To 403 'Company 1 ' CO200 = CO200 + cell.Offset(0, 1).Value ' If cell.Offset(0, 6) = 30 And cell.Offset(0, 9) < "X" Then ' CO200del = CO200del + cell.Offset(0, 1).Value ' End If End Case End if Next the above pseudocode may give you some insights. -- Regards, Tom Ogilvy "Marie" wrote in message ... Thanks for your help! But, I have to use a macro because there is other select criteria I have to pick up after the two amounts are pulled (that I have set up as Case 302 etc) for example this is right below the selection of the two columns: Case 302 To 303, 400 To 403 'Company 1 ' CO200 = CO200 + cell.Offset(0, 1).Value ' If cell.Offset(0, 6) = 30 And cell.Offset(0, 9) < "X" Then ' CO200del = CO200del + cell.Offset(0, 1).Value ' End If I just need to know the line I need to add to pick up the second group before all the select criteria , or am I all wet in thinking it can be done that way? I thought if it was before the case selection it would automatically add it to - for example, CO200. "Tom Ogilvy" wrote: do you have to use a macro =SUM(IF((A1:A10=DATEVALUE("1/1/04"))*(A1:A10<=DATEVALUE("3/31/04"))+(C1:C10 =DATEVALUE("1/1/04"))*(C1:C10<=DATEVALUE("3/31/04"))0,B1:B10)) Entered with Ctrl+shift+Enter should do it. -- Regards, Tom Ogilvy "Marie" wrote in message ... I'm thinking this must be impossible becasue I haven't received any response. If there is anybody pretty knowlegeable and they know it is impossible or think it is would you please tell me and I will stop driving myself crazy trying to figure it out 1st Column 2nd column 3rd Column 3/31/04 $500.00 2/1/02 2/01/01 600.00 1/15/04 I want to pick up any dollar amount that falls within the 1qt 04, whether it is the first or third column. So I should pick up $1,100.00. I had a code that picked up the first column but of course somebody always has to change something, and now they tell me if the 3rd column has a 1qt 04 date they want it also. This is the code I am trying to use. If anybody would know of another site that would explain this or a book that I could read about this or ANYTHING, I would be forever greatful!!! Please Application.Worksheets("Dates").Columns("g").Numbe rFormat = "mm/dd/yy" Application.Worksheets("Dates").Columns("l").Numbe rFormat = "mm/dd/yy" For Each Cell In Range("NoteList") Cell.Offset(0, 5).Value = Cell.Offset(0, 5).Value 'this being the first date Cell.Offset(0, 10).Value = Cell.Offset(0, 10).Value 'this being the second date If Cell.Offset(0, 5) Range("HistDate") And Cell.Offset(0, 5) <= Range("controldate") Then If Cell.Offset(0, 10) Range("HistDate") And Cell.Offset(0, 10) <= Range("controldate") Then Select Case Cell.Offset(0, 2) 'this being the amount |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum columns between certain dates | Excel Discussion (Misc queries) | |||
adding columns if dates are the same | Excel Worksheet Functions | |||
How can I insert columns of dates in between exsisting columns of | Excel Discussion (Misc queries) | |||
dates in columns | Excel Discussion (Misc queries) | |||
Filtering dates in two different columns | Excel Programming |