Conditional Formatting for Whole Number and Date

[Joyner]

Hello, I am trying to do two different conditional formats (different cells not the same cell)

The first one needs to determine if the cell contains a whole number, if not, show the formatting. So if it is not a number show the formatting, and if it is a number but not a whole number show the formatting. I have this formula that seems to results in the correct True or False, but it doesn't want to work in conditional formatting?

=IF(ISNUMBER(X3),(X3-INT(X3))=0)

How come it results in the correct result for a formula but doesn't seem to work for conditional formatting?

Also, I want to do conditional formatting to check if a cell is a date, if not, show the formatting. Is this possible with conditional formatting, or do I need code?

Thanks for any help

 

[T. Valko]

Hello, I am trying to do two different conditional formats (different cells not the same cell)

The first one needs to determine if the cell contains a whole number, if not, show the formatting. So if it is not a number show the formatting, and if it is a number but not a whole number show the formatting. I have this formula that seems to results in the correct True or False, but it doesn't want to work in conditional formatting?

=IF(ISNUMBER(X3),(X3-INT(X3))=0)

How come it results in the correct result for a formula but doesn't seem to work for conditional formatting?

Also, I want to do conditional formatting to check if a cell is a date, if not, show the formatting. Is this possible with conditional formatting, or do I need code?

Thanks for any help

To test a cell for a whole number:

=AND(COUNT(A1),INT(A1)=A1)

To test that a cell contais a date:

Well, this one can get tricky. In Excel dates are really just numbers formatted to look like dates. So, to test a cell to see if it contains a date we just need to see if the cell contains a number.

However, if the cell might otherwise contain a number like 100 (or whatever) then that's a problem.

One way to account for the difference between numbers and dates is to test for a specific date range. Like, is the date in the cell >=X and <=Y.

See if this does what you want:

=COUNT(A1)

 

[Joyner]

Thanks for the reply Biff,

I used your idean regarding the date and that works. The suggestion for the whole number works if the cell contained a number, but didn't if the cell contained a non-number.

I was able to work it out using If(isnumber.

Thanks

 

[T. Valko]

Thanks for the reply Biff,

I used your idean regarding the date and that works. The suggestion for the whole number works if the cell contained a number, but didn't if the cell contained a non-number.

I was able to work it out using If(isnumber.

Thanks

Ok, good deal. Thanks for the feedback!