Vlookup Error when referencing date

**NFW1976** · 08-22-2013, 08:45 AM

Hi, this is my first post!

I have the following formula which works if I manually enter a date into cell F1 e.g 06/13 which displays as Jun-13

Please Login or Register  to view this content.

However cell F1 contains =B1-90, to take the date in B1 minus 90 days to give the previous quarter. In cell B1 I have 01/09/13 which displays as Sep-13 which in turn displays Jun-13 in cell F1.

Why does my formula only work when I manually enter the date into F1? Is there a workaround to keep the =B1-90?

**NFW1976** · 08-22-2013, 09:06 AM

Hi, I've now solved this - typically straight after posting!

I realised that although both cells were displaying Jun-13 the Jun-13 in the vlookup table had in fact been entered as 01-06-13.

Thanks anyway!

**XOR LX** · 08-22-2013, 09:14 AM

Hi,

Have you checked that you actually do have a match for the value of B1-90 in your first lookup range ($A$57:$B$68)?

Also, be careful: when you enter, as you say, "06/13", Excel indeed recognises this as date format, but also, since you have not entered a day (only month and year) and, since Excel requires a day for all date entries, it defaults to the first of that month. Hence, the actual date in that cell is 01/06/2013.

So that makes B1-90 equal to 03/06/2013 and that is the precise value you need to ensure is in your lookup range.

Regards