HLookup problem with dates

[Guest]

If today's date is June 7, 2007 and formula in B4 on Monthly Expense
spreadsheet is =Now()-33 (to Sat May 5, 2007) the HLookup formula in column
D, rows 5 - 24, of the CPH Master spreadsheet does not work (pulls in data
from wrong column). However, if the formula in B4 on the Monthly Expense is
=Now()-32 (to Sun, May 6, 2007) the HLookup formula in Column D, rows 5 - 24,
of the CPH Master does work.

Example formula in row 5 of the CPH Master is: =HLOOKUP('Monthly
Expense'!$B$4,'Monthly Expense'!$C$4:$P$22,3,TRUE)*'CPH Master'!$B$28

If today's date is June 8, 2007 and formula in B4 on Monthly Expense
spreadsheet is =Now()-34 (to Sat May 5, 2007) the HLookup formula in column
D, rows 5 - 24, of the CPH Master spreadsheet does not work (again, pulls in
data from wrong column in look up table). However, if the formula in B4 on
the Monthly Expense is =Now()-33 (to Sun, May 6, 2007) the HLookup formula in
Column D, rows 5 - 24, of the CPH Master does work.

This report has run for a couple of months for a variety of sites (all using
the same template) and this is the first time this problem was noticed. It
appears to have something to do with the dates and not the formula. The
effectiveness of the formula in both described incidents changes with the
date, not the formula.

Any ideas?

 

[Bernard Liengme]

Try using TODAY() rather than NOW() and tell us if this works
You seem to want only the date not date+time
best wishes

 

[Bernard Liengme]

Expanding: with NOW()-33 you might get something like 12.89 which HLOOKUP
will take to be 13 not 12

 

[Guest]

Using TODAY() rather than NOW() had not affect on the results.

 

[Peo Sjoblom]

Are you looking for an exact match then you should replace TRUE with FALSE,
if not the lookup column needs to be sorted in ascending order
Also check that the dates are not text

 

[Guest]

If I put FALSE in the formula I get #N/A as the value in the HLookup cells.
The values I am looking up are monetary values.

The dates are not text but are formated as dates (MAR-01).

Thank you....Any other thoughts?

 

[Peo Sjoblom]

What happens if you copy one of those dates into a cell, then format the
cell as general, for instance if (Mar-01) is March 1st 2007 it should return

39142


if it is March 1st 2001 it should return


36951

if it returns decimals as well then you have to factor in time as well


if it returns (MAR-01) with general format it is text

You didn't answer if you are looking for an exact match, if you do you
should use FALSE or you will get erroneous data

FALSE will return #N/A if it can't find a match, that means what you think
is a match is not

There is nothing else wrong, just the underlying data that are not the same

If you select one of the dates in the lookup column you should see the short
date from your regional setting in the formula bar, if you see (Mar-01) it
is text

You should never use NOW comparing dates unless you want the time compared
as well, use TODAY

 

[Guest]

THANK YOU! I found the problem. You were correct when you mentioned the
reason behind the #N/A return with the False in the formula. When I was
looking at the headers in the look up table, they were being displayed as
month and year (May-07) but the date, when I changed for format was 5/6/07
and I was looking for 5/1/07.

Again, thank you so much.