if function for date not working

I have a spread sheet the has 2 sheets(sheet 1 is equipment rental company bill, sheet 2 is my tracking of rented equipment usage, Sheet 2 has a vlookup formula =VLOOKUP(G4,Table1[[Chassis ID]:[Hire Date]],2,TRUE) in columns P and Q where it returns the start and end date when the piece of rented equipment matches on both sheets 1 and 2, columns H and I are the dates i tracked that we had the equipment. I have entered an if formula in column U =if(H4=P4, "yes", "no") I can blatantly see that H4 does in fact equal P4 with a date of 10/4/2016. I have formatted all the columns as dates with the same formatting scheme. Crap, I think I just found part of the answer, but now still don't understand why its only half working. Part of it was one column was stored as text originally and one a date/time so the date time would display and integer when changed to text. I entered a new column with =TEXT(Table2[[#This Row],[Start date Match]],"mm/dd/yyyy" to change them all to text. But there are still half of the column returning "no" when they should be returning "yes". I can't think of anything else.

If we are to help you, we will need to see a sample workbook illustrating the problem.

Here it is

View attachment TRAC_Invoice_JAPJ8319_BillDetails (1).xlsx

Hi,

a small fix: I hope it could be of some help.

=TEXT(Table2[[#This Row],[Start date Match]],"m/d/yyyy"


Regards

Hello
The problem is because you have provided a specific date format for the text in R expressing the day number as two characters. This means that all the dates in this column will be a total of 10 characters. (eg "10/04/2016" )
However..... there is no text formula in column H, and the dates will be as entered. When the day number is less than 10 there are only 9 characters (eg "10/4/2016") which is a mismatch when they are compared equal.
You can fix this by using Canopones formula edit on column R.

Hope that helps to explain things for you.

Sorry no, when you use "m/d/yyyy" excel thinks there is a inconsistent calculation column formula, and won't return m/d/yyyy it only shows the formula then. Here is the fix that I came up with after sleeping om it. Copy the dates that are stored as text out of excel into a word document, paste special as text only, change the formatting to date in the excel columns they came out of, then copy and paste them back in, excel then thinks they are dates and =if(H4=P4, "yes", "no") works just fine.

Hello
Well done for finding your own solution
However, as your working with tables it might be worth understanding the other approach suggested as its pretty neat. The error you mentioned will be because you made a typed amendment to the formula to one cell in Col R. Because its part of a table column Excel then tries to adjust all the other formula entries in that column and was unable to complete this for some reason, and leaves the formula there for you to make further amendments.
Instead of typing an amendment in that way if you simply paste Canopone's formula into a cell in column R, this targets all the inconsistent formats in the column bringing them into line with the text in Col H. Very useful to know !

Apologies for perpetuating this thread, but this problem puzzled me because I could'nt find any data inconsistencies to explain the failure to update the formulae. It turns out that the text formatting of the [Start date] table column prevents Excel from treating this as a calculated table column causing the edit process to hang.
If the format is amended to General, any cell can then be edited to =TEXT([@[Start date Match]],"m/d/yyyy") and this will be replicated throughout the column, resolving the problem.