day not recognized with IF?

[Harry0]

A1 is
3/11/15

B1 is
=A1 (Wednesday)
(formatted cell to show only day, DDDD)

C1 is
=IF(B1="Wednesday","true","false")
C1 is always false
WHY? file attached if it works for you well it does not work for me. Maybe I am doing something wrong or something that is not allowed.

On another note. Many times it seems like basic things that should work but don't work. Even basic calculations that reference another line give an error despite all numbers. Some say say to do it again but why does it happen in the first place?

 

[Mike86]

Try converting A1 to text using:

=text(a1, "DDDD")

Should work then.

 

[NARAYANK991]

Hi Harry ,

The first thing to understand is that a cell format merely changes the way the cell contents are displayed. It does not change the cell contents in any way.

Thus , if you have a date in a cell , that date is just a number ; a date alone is an integer number , while a date which has a time component as well is a decimal number.

For example , today's date ( 11 March 2015 ) is just the number 42074.

The output of the Now() function , which is a date with a time component , would be 42074.8055653935 , which in a more understandable format would be :

11-03-2015 7:19:09 PM

Thus , just formatting a cell to display Wednesday , does not take away from the fact that the cell contains the number 42074.

This will never be equal to the text string Wednesday.

The only way to check for equality would be if you converted the numerical date value to a text string using the TEXT function ; your IF statement would then be :

=IF(TEXT(B1,"dddd") = "Wednesday" , TRUE , FALSE)

Narayan

 

[Harry0]

Thanks those things worked.

It would be more versatile to work the other way too. But for processing and storage reasons I guess it is what it is. Maybe in another 10 years when computers are more powerful there will be more than one way to "skin the cat" as the saying goes.

 

[KenU]

@Harry0

Another option would be to use the WEEKDAY function to return the number (from 1 to 7) of the weekday and use that for the comparison. The formula above would then be:

=IF(WEEKDAY(B1)=4,TRUE,FALSE)

As an extra benefit, numeric comparisons are faster than string comparisons, but this would only be evident with a large number of them.

Hope that helps.

Regards,
Ken

 

[NARAYANK991]

Hi ,

I think the other benefit would be if you develop workbooks to be used internationally , where languages can differ ; the check using WEEKDAY would always work , whereas Wednesday would be recognized only if the language were English.

Narayan