Formula to count back 30 days from B103

tinstaafl

I have a spreadsheet where column B contains dates (all in chronological order). Some dates are listed more than once, some dates do not appear at all.

As part of a VLOOKUP I need a formula to go back up column B from B103 (which has the date "Feb 10, 07") to the cell containing the date 30 days earlier. This is the first time I'm doing a VLOOKUP on my own and I think it will work, except I can't get past this problem.

I may have another problem when there is no date 30 days earlier but I think I can solve that one.

I've tried OpenOffice.org. Help as well as "Excel Formulas for Dummies" but could not find an answer.

Thanks,
Phil
_________________
Traveling the highways of America since February 2000 (http://www.stringbean.com)

geoff80fg · OOo Advocate Joined: 26 Jul 2006 Posts: 420 Location: UK

Try:

=VLOOKUP(B103-30;B80:B103;1;1)

and format the cell for Date (you might have to alter the range - the second bit - as well, I've just used enough to cover 30 days)

Hope this helps

Geoff

TerryE · Super User Joined: 16 Jul 2006 Posts: 550 Location: UK

The feature that geoff is using here is that dates are store internally as a double counting in units of a day from a base date/time of 30 December 1899 00:00:00.
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now.

tinstaafl · Posted: Sun Feb 11, 2007 5:51 am Post subject:

Thanks guys, I've got it. The problem I was having was that in looking for the proper syntax for the date part I kept trying variations of "=B103-1" knowing that I'd get "Feb 9, 07" if I was doing it right. Turns out I was doing it right but I was doing it in a cell that was not formated for dates. What a dork!

The formula I was using, that does work, is:
=IF(ISBLANK(B103);"";(J103-VLOOKUP($B103-30;$B$3:$J$1000;9;0))/30)

Thanks again,
Phil
_________________
Traveling the highways of America since February 2000 (http://www.stringbean.com)