Results 1 to 8 of 8

Thread: if function for date not working

  1. #1

    if function for date not working



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    If we are to help you, we will need to see a sample workbook illustrating the problem.

  3. #3

  4. #4
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    99
    Articles
    0
    Excel Version
    Excel 2010
    Hi,

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

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


    Regards

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    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.

  6. #6
    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.

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    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 !

  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •