OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Formula to count back 30 days from B103

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
tinstaafl
General User
General User


Joined: 13 Sep 2006
Posts: 43
Location: America's Highways

PostPosted: Sat Feb 10, 2007 8:27 pm    Post subject: Formula to count back 30 days from B103 Reply with quote

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)
Back to top
View user's profile Send private message Visit poster's website
geoff80fg
OOo Advocate
OOo Advocate


Joined: 26 Jul 2006
Posts: 420
Location: UK

PostPosted: Sun Feb 11, 2007 12:01 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
TerryE
Super User
Super User


Joined: 16 Jul 2006
Posts: 550
Location: UK

PostPosted: Sun Feb 11, 2007 5:00 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Visit poster's website
tinstaafl
General User
General User


Joined: 13 Sep 2006
Posts: 43
Location: America's Highways

PostPosted: Sun Feb 11, 2007 5:51 am    Post subject: Reply with quote

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)
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group