Forum Discussion

muhun2's avatar
muhun2
Copper Contributor
Jan 30, 2024

Error in the logical test section of the IF function

Hello everyone.

 I've got a problem in excel function & I need your help.

 Now I'm using Excel 2016 in MS, PC.

 

On the calendar date, the date is entered using the following command, and the 'cell format' is changed by entering d in the customization.

={1,2,3,4,5,6,7} -WEEKDAY(DATE($B$2,$C$2,1),1) +DATE($B$2,$C$2,1)

 

 Table name in another worksheet: In the table that is an annual ledger
=DATE (VALUE([@년], VALUE([@월], VALUE([@일]))) and add dates.

 

 This column and the date of the calendar
=IF (C5=연차대장[날짜종합], 연차대장[텍스트],"")

 I made an IF statement using and performed a logical test, but I always get a false result and I get a blank.

 The 'cell format' of the date comprehensive column in the annual ledger table is selected *2012-03-14 in the date category.

 

 I don't know why the logical test only shows false.
 I would appreciate it if you could answer me.

 

Thanks for reading my question.

excel
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jan 31, 2024

    muhun2 

    It seems like there might be an issue with the comparison of the dates in your IF statement. When working with dates in Excel, it's crucial to ensure that the dates are in the correct format and that there are no hidden characters or discrepancies.

    Let's break down the components:

    1. Calendar Date Formula:

    ={1,2,3,4,5,6,7} - WEEKDAY(DATE($B$2,$C$2,1),1) + DATE($B$2,$C$2,1)

    This formula generates a list of dates based on the input year and month. Make sure that the result is in a valid date format.

    1. Annual Ledger Table Date Formula:

    =DATE(VALUE([@년]), VALUE([@월]), VALUE([@일]))

    This formula should also result in a valid date.

    1. IF Statement:

    =IF(C5=연차대장[날짜종합], 연차대장[텍스트],"")

    Here, you are comparing the date in cell C5 with the date in the "날짜종합" column of the "연차대장" table. Ensure that both dates are in the same format and there are no hidden characters.

    Here are a few troubleshooting steps:

    • Date Format Consistency: Confirm that both date columns have the same date format. You mentioned that the "날짜종합" column is formatted as *2012-03-14. Ensure that the dates from the calendar formula are also in this format.
    • Data Types: Make sure that the data types of the date columns match. If there are discrepancies, you may need to convert one of them to match the other.
    • Hidden Characters: Sometimes, there might be hidden characters in the cells. You can use the CLEAN function to remove non-printable characters.

    =CLEAN(C5)

    After checking these aspects, if the issue persists, you may want to consider providing more details about the data or sharing a sample of the spreadsheet for further assistance. The text, steps and functions were created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

    • muhun2's avatar
      muhun2
      Copper Contributor
      Feb 01, 2024

      NikolinoDE 

      Thank you for answering my question.

      I check all aspects you mentioned. But, the issue isn't fixed.

      I upload my excel file.

      I'll appreciate if you could check this.

      Bless you

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        Feb 01, 2024
        Excuse me, but after trying with the file, I see that my Excel is getting confused with the characters and at the same time I can't decipher each of these characters.
        Please forgive me if I can't be of more help here.
        Maybe someone who follows this message can say and know more about it.

        Thank you for your time, patience and understanding.

Resources