IF function not working.

The UK's most regular instructor-led training courses.
Training information: excel classes · Microsoft excel training · Microsoft Excel Training London
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · High Priority · Version 2003

28 posts

ReplyWed 24 Jun 2009, 09:24Delegate Gareth said...

Gareth has attended:
Excel VBA Intro Intermediate course
Excel Intermediate course
Excel Advanced course

IF function not working.

Hey

I have a bit of a problem with my If Function.

I have two dates in two seperate colums. in the format dd/mm/yyyy. The columns are W and Q.

I have input an If Function along the lines of =If (W2=Q2), "", "Re-allocate"

The problem i'm having is that when the two dates match in both columns the formula is still returning "Re-allocate".

I think the problem might lie with the formatting of column Q. The date shown is the date retrieved from a vlookup function, from another sheet, that includes a time of day as well such as 00:00:00. The v lookup is only returning the date however.

Any thoughts as to why this is not matching? or Excel not recognising the two as the same date?

Thanks

Version	Date	Location	Places available	Book	Next place rate (£)
Excel VBA Intro Intermediate 2 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice
2003	Thu 18 + Fri 19 Mar 2010	Southwark	0	FULL
2003	Mon 22 + Tue 23 Mar 2010	Bloomsbury	2		£495	£495
2007	Tue 6 + Wed 7 Apr 2010	Bloomsbury	4		£470	£475
2003	Wed 7 + Thu 8 Apr 2010	Southwark	7		£445	£450
2007	Thu 15 + Fri 16 Apr 2010	Bloomsbury	0	FULL
2003	Wed 21 + Thu 22 Apr 2010	Bloomsbury	8		£445	£450
Full Schedule: See all 36 Excel VBA Intro Intermediate course dates. Bookings currently available until 22nd December 2010.

2,024 posts

ReplyWed 24 Jun 2009, 09:47Trainer Amanda said...

RE: IF function not working.

Hello Gareth

Thank you for your question.

Would it be possible for you to email in a copy of the spreadsheet (or a similar example), including the sheet that the vlookup data? If so please email to amanda

Kind regards
Amanda

28 posts

ReplyWed 24 Jun 2009, 10:30Delegate Gareth said...

RE: IF function not working.

Thanks amanda. Example Copy sent.

2,024 posts

ReplyWed 24 Jun 2009, 11:42Trainer Amanda said...

RE: IF function not working.

Hello Gareth

Thank you for sending through the spreadsheet.

I don't think that the problem lies in the formatting, I think it is to do with the fact that you are getting Excel to try and compare something that is a value (the date in column A) with something that is the result of a formula (the result of the lookup in column C) - you're not comparing apples with apples here.

I tried copying the date from column C and pasting it as a value in a different cell, and changing the IF function and then it worked. I can't think of an easy solution for this, but I hope this helps to explain why the formula isn't working as you expect.

Kind regards
Amanda

28 posts

ReplyWed 24 Jun 2009, 12:24Delegate Gareth said...

RE: IF function not working.

Thanks Amanda

Im a bit confused by this one as it worked with the formula result and text date comparison yesterday!

I amended the tab the vlookup was collecting the data from this morning and now it no longer works!

· Employing Cell References in Excel
· Tools From Advanced Excel Courses: Goal Seek and Solver
· Compare Different MS Excel Training Methods
· Excel Advanced Courses Ease the Frustration of Learning Complex Tasks
· Excel and Inventory Management

Please browse our web site to find out more about
excel classes and other Microsoft training courses.

Excel tip:

Deleting a range of cells using the autofill handle

Firstly, select the range of cells for which you would like to clear the contents. Then drag the autofill handle to the the top left corner of the selection whilst holding down the shift key. Your selected contents should then be deleted.

View all Excel hints and tips