Excel formula help

[masteraleph]

Unfortunately my strength with Excel is weak, and I'm not entirely sure what to search for in general...

I have a spreadsheet with a series of dates in Row 1 (with the exception of A1), data corresponding to the date in rows 2-12, and then a series of formulas below that. The dates are not completely consecutive; they correspond to "weekdays," and as such exclude weekends and holidays. I'd like to track certain changes over certain time periods, such as 1 day, 1 week, 1 month, etc.

Here's the question: if the date in the column is, say, 08/31/2010, and I want to have Excel select a cell from a column one month prior, is there a way to phrase that in a formula? (i.e., it would select a cell from the column starting with 07/31/2010, or, since that was a Saturday and isn't in the chart, select from the row starting with 07/30/2010 instead). The same question applies to 1 week prior, or 1 year prior, for that matter.

I'm suspecting that this is either significantly easier or significantly more complicated than I think it is; either way, I'd appreciate help, or at least alternative suggestions.

 

[PTNL]

You can use the DATEDIF function for some of this.

To keep things easy on yourself, try splitting up the formula's logic into separable pieces. Makes troubleshooting easy, and ensures you see the correct information at each step. You can combine and refactor later once you're sure the approach you're trying is successful.


Edit: You'll likely need to write a little VBA to look for a particular cell by its value. Here's one example.

 

[masteraleph]

You can use the DATEDIF function for some of this.

To keep things easy on yourself, try splitting up the formula's logic into separable pieces. Makes troubleshooting easy, and ensures you see the correct information at each step. You can combine and refactor later once you're sure the approach you're trying is successful.


Edit: You'll likely need to write a little VBA to look for a particular cell by its value. Here's one example.

Excellent! I'm probably going to skip the VBA for right now and do it by splitting up the formulas and using a combination of DATEDIF and IF (probably mostly on a second sheet). Many thanks for the help.