Excel Date

  weatherstation 19:45 14 May 09
Locked

Hi everybody. I am trying to get Excel to look up a date in one workbook, and if the date is correct preform a calculation. The cell it is looking up is in the date format, and if I change the cell to a number format and put a number in the formula works correctly. But I would ideally like to work with a date. The formula below is what I have entered. Can anybody tell where I have gone wrong. Thanks.

IF('[Electric Prices No 2.xls]Sheet1'!$A$48)>= 01/05/2010,('[Electric Prices No 2.xls]Sheet1'!$A$51),"")

  johnnyrocker 19:47 14 May 09

vog will have the answer



johnny

  Simsy 20:22 14 May 09

but two thoughts spring to mind...

01/05/2010 might be being seen as a division.
if it's not, have you tried Value(01/05/2010)

Regards,

Simsy

  weatherstation 20:24 14 May 09

Sorry folks, but I left 2 quotation marks off the first formula when I posted my query. Thanks to johnnyrocker for his quick response, but I do not know who or what vog is.

IF('[Electric Prices No 2.xls]Sheet1'!$A$48)>= "01/05/2010",('[Electric Prices No 2.xls]Sheet1'!$A$51),"")

  weatherstation 20:27 14 May 09

Hi Simsy and thanks for info. Yes I have tried your suggestion, but it did not work.

  VoG II 20:29 14 May 09

Try

IF('[Electric Prices No 2.xls]Sheet1'!$A$48)>= DATEVALUE("01/05/2010"),('[Electric Prices No 2.xls]Sheet1'!$A$51),"")

or maybe

IF('[Electric Prices No 2.xls]Sheet1'!$A$48)>= "01/05/2010"+0,('[Electric Prices No 2.xls]Sheet1'!$A$51),"")

  weatherstation 20:32 14 May 09

Hi Simsy. Sorry about my last post to you. I left Value you out when I tried it. I put Value("01/05/2010") and the formula works. Thanks very much for your help.

  TonyV 20:39 14 May 09

Using VoG™'s second method, in a simpler form, it certainly worked. I used =IF(A2>="01/05/2010"+0,B6,"") which is essentially what you have but I used only one sheet for the example.

TonyV

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

Where HTML5 is headed next

MacBook Pro v Surface Pro 5