Selecting dates

[brjohnson]

Hi,

I'm wondering if there is a quick way to select values tied to dates from a list that are X days after the starting date, with X being a variable.

For example: If I start 1/1/2000 and chose X=30 days - is there a formula I can apply to capture the 1/1/2000 value, and then every subsequent 30 day value through present?

Thanks!

 

[scottylad2]

Excel Workbook
	J	K
7	Date	Variable
8	01/01/2000	30
9	31/01/2000
10	01/03/2000
11	31/03/2000
12	30/04/2000
13	30/05/2000
14	29/06/2000
15	29/07/2000
16	28/08/2000
17	27/09/2000
Sheet1

You mean like this?

 

[borgem]

If you want to ensure that it runs only through the current days date (or as near as the increment takes it), then I came up with a combination of formula and macro, only because I couldn't come up with the macro code alone that would produce the desired results.

Assuming you have the headers and start date set up like scottylad2 shows, in any given cell (in my case C1) enter in the formula "=INT((TODAY()-($A$2))/30)" << This cell will need format "General" or "Number"

Then you will want a macro like this:

Sub Macro1()

    With Sheets("Sheet1")

    '.Range("A3") = .Range("A2") + 30
    Dim i As Integer
    For i = 3 To Range("C1")
        .Range("A" & i) = .Range("A" & (i - 1)) + 30
    Next i
    End With
End Sub

Then you could hide the cell with the formula and voila!

 

[brjohnson]

Scotty - thanks for the quick reply.

As a follow up - is there a way to return a certain value if a date falls within a certain range?

For example - if on 1/1/2000 XXX Value is returned and on 2/1/2000 YYY Value is returned...

I want to display XXX for all dates 1/1/2000 - 1/31/2000
and
YYY for 2/1/2000 to 3/1/2000 and so on...

Basically I need a if date is within X days of this date then return the value function.

 

[scottylad2]

not entirely sure what your trying to do, can you provide a sample of what you have and what you're trying to achieve? ie your layut and expected result

 

[brjohnson]

Sure-

I have two tables. One with a date and corresponding results, longer time period (in this case monthly). And a second table which has each date (daily) and I want the results from the monthly input into the daily.

So:

If we have a longer time period table (in this case monthly):

1/1/2000 : XLB
2/1/2000 : IYR
3/1/2000 : IWM

I want to display those results in the daily table as such:

1/1/2000 : XLB
1/2/2000 : XLB
1/3/2000 : XLB...
1/31/2000 : XLB
2/1/2000 : IYR
2/2/2000 : IYR...
3/1/2000 : IWM

the longer time period values fill in the gaps in the daily time period table.

 

[scottylad2]

Not really sure how this will tie in with your original question but maybe this

Excel Workbook
	K	L	M	N	O
19	Monthly	Values		Daily	Values
20	01/01/2001	ABC		01/01/2001	ABC
21	01/02/2001	DEF		02/01/2001	ABC
22	01/03/2001	GHI		03/01/2001	ABC
23	01/04/2001	JKL		01/02/2001	DEF
24	01/05/2001	MNO		02/02/2001	DEF
25	01/06/2001	PQR		03/02/2001	DEF
26	01/07/2001	STU		04/02/2001	DEF
27	01/08/2001	VWX		01/03/2001	GHI
28	01/09/2001	YZA		02/03/2001	GHI
29	01/10/2001	BCD		03/03/2001	GHI
30	01/11/2001	EFG		04/03/2001	GHI
31	01/12/2001	HIJ		05/03/2001	GHI
Sheet1

this will match a value where the month is the same, if your crossing into a following year you'll need to make a match on both the month and year. Needs to be entered with ctrl + shift + enter

 

[brjohnson]

Thank you again!

I'm actually trying to do the reverse of what you just explained. Start with monthly values and populate daily. I think I've got it figured out aside from two issues - copying these ctrl shift enter cells. How is that done?

And then if you could show how to match the year as well I'd appreciate it.

Thank you so much for your help. I have put probably 10 hours into figuring out what you answered in just a few minutes of your time. SOOO appreciated.

 

[brjohnson]

After getting deeper into this it looks like the function won't work for what I'm doing.



Uploaded with ImageShack.us

As I'm advancing thirty days at a time there are situations where I have multiple references in the same month. This appears to cause errors. Any ideas?

 

[scottylad2]

I'm not sure what way you're adapting it, hopefully you can adapt this. Now it's looking at both the month and year

Excel Workbook
	K	L	M	N	O
19	Monthly	Values		Daily	Values
20	01/01/2001	ABC		01/01/2001	ABC
21	01/02/2001	DEF		02/01/2001	ABC
22	01/03/2001	GHI		03/01/2001	ABC
23	01/04/2001	JKL		01/02/2001	DEF
24	01/05/2001	MNO		01/01/2003	HIJ
25	01/06/2001	PQR		03/02/2001	DEF
26	01/07/2001	STU		04/02/2001	DEF
27	01/08/2001	VWX		01/03/2001	GHI
28	01/09/2001	YZA		02/03/2001	GHI
29	01/10/2001	BCD		03/03/2001	GHI
30	01/11/2001	EFG		04/03/2001	GHI
31	01/01/2003	HIJ		05/03/2001	GHI
Sheet1