Excel lookup same day different date previous year

January 4, 2010 5:12 PM

Quick Excel help? Need to retrieve this day last year based on date input by user

My plan is to make a sheet with all of last year's sales by day, and then have other weekly sheets for this year reference that sheet. So when making the new schedule for example, when the user changes the date to start on Tuesday the 12th, I need it to look at last year on Tuesday the 13th, and populate a field for previous year's sales. I've searched a bunch, but all I've grasped so far is exact matches. Thanks in advance.

posted by hypersloth to Computers & Internet (15 answers total)

Best answer: The quick and dirty way to do this, I suppose, is just to subtract 364 from the date your user enters to start, and then use that as the basis for your lookup. Excel just stores dates as integers, so you can do a regular addition or subtraction operation on them to calculate another date.

I'm sure there are more elegant ways of accomplishing this, and of course you would have to change the formula if you were calculating over a leapyear.
posted by strangely stunted trees at 5:38 PM on January 4, 2010

Best answer: The difference between the dates will be the difference between the first days of the year. If you've got Excel 2007, this will be "=WeekDay(Date(ThisYear,1,1))-WeekDay(date(ThisYear-1,1,1))" Add this value to the day of the year of the previous year to get the date. Assuming that you're pulling the data as CurrentDate, a solution will be:

"=Date(Year(CurrentDate)-1,1,1) + (CurrentDate - Date(Year(CurrentDate),1,1)) + WeekDay(Date(Year(CurrentDate),1,1))-WeekDay(Date(Year(CurrentDate)-1,1,1))"
posted by FuManchu at 5:47 PM on January 4, 2010

Response by poster: Well, I knew it would be quick; thank you. Seems so obvious now, but I was afraid I was going to have to decipher Excel's actual date interpretation and figure 6-digit numbers for dates.. I had also just come back from checking when leap year was when I saw your answer - not for a couple of years, so I'm happy with this for now. Thanks again!
posted by hypersloth at 5:50 PM on January 4, 2010

Fu, doesn't that still break down if the calculation period includes a February 29?
posted by strangely stunted trees at 5:55 PM on January 4, 2010

Response by poster: FuManchu, I'm not sure if I'm reading that right, but the sheet has to do it based on the user inputting the date. So what I have after sst's solution will be something like =VLOOKUP(Sheet1!A2-364,Sheet2!A2:G10,2,FALSE)
posted by hypersloth at 5:57 PM on January 4, 2010

Response by poster: (which is Excel 2003, what I have at work)
posted by hypersloth at 5:59 PM on January 4, 2010

Yea, don't worry about it if you don't need a general solution. It's trickier to implement that in the 2003 version. Go with strangely stunted trees' solution.
posted by FuManchu at 6:01 PM on January 4, 2010

Best answer: If you want to control for leap years just use this:

=(MONTH(A1)&"/"&DAY(A1)&"/"&YEAR(A1)-1)+1

... where A1 is the date that was entered by the user. This will give you the day after the date that is entered by the user (minus one year) whether it's a leap year or not.

You will want to make sure that cell in which this formula is entered is formatted as a date. After you write this formula, it will probably default to a number format -- just choose Format > Cell > and choose Date from the list.
posted by crapples at 7:12 PM on January 4, 2010

Best answer: or:

=EDATE(A1,-12)+1
posted by pompomtom at 7:16 PM on January 4, 2010

For the record, in case you're still reading this, pompomtom's solution is definitely best.
posted by crapples at 4:58 AM on January 5, 2010

I thought EDATE is only in Excel 2007, which doesn't help her. Besides, that too only works for this year, and not any others. It's no better than subtracting 364. You'd need a different function for each new year.

SST, my solution needs would work generally, even with a leapyear. It counts the number of days from the beginning of the year, and adjusts it for the difference in the day of the week. Actually, you'd need to use MOD(WeekDayA - WeekDayB,6) or something to ensure it worked forever. I don't believe 2003 has the WeekDay function, which makes that useless.
posted by FuManchu at 5:13 AM on January 5, 2010

her the OP
yikes
posted by FuManchu at 5:17 AM on January 5, 2010

According to this EDATE() is in 2003 - as is WEEKDAY().

That said, I think we're operating on two different ideas of what the question is, what with all this weekday stuff. I just read it as basically separating time into non-overlapping years (hence 13/n/2008->12/n/2009, or whatever).
posted by pompomtom at 1:21 PM on January 5, 2010

Oh geez. I've been entirely unhelpful, sorry.
posted by FuManchu at 5:03 AM on January 6, 2010

Response by poster: Thanks again everyone - I couldn't get pompomtom's to work, but it might just be my own excel illiteracy - here's what I ended up with
=VLOOKUP((MONTH(B4)&"/"&DAY(B4)&"/"&YEAR(B4)-1)+1,[prevyear.xls]dailysales09!$A:$D,3,FALSE)

When I tried substituting EDATE, I got either a value error or too few arguments error - tried reformatting the cells, playing with parentheses, etc. Like I said, probably just an error in my syntax, but this worked.
posted by hypersloth at 4:59 PM on January 7, 2010