Excel Dates?

Question

I am trying to get a range to return blank if it does not meet my criteria, does anyone know how I can do this.

Here is my problem, Februray has 28 days but 29 days in each leap year, I want the range of cells to return blank if the cell is not the 29th. I am using the =DATE(YEAR(CellRef),MONTH(CellRef),DAY(CellRef) to calculate the days, so for this year it's showing as 01/03/07, i have a numerical column that lists the days ie 1-29 and then my formula to calculate the actual dates, I then have another field that calculates the days ie Monday to Friday which changes for me year on year, but Im having this slight problem with February, I think it's because my brain has gone to sleep...lol All i want to do is mark both the Numerical column and the day column blank if the date does not equal the 29th Feb. If anyone knows how I can accomplish this, I would be most grateful.

Answer 1

Assuming you have dates in column A the following formula in column B will test if the month AND day are FEB28 , If so then the IF statment will write "2/28" if not it leaves the cell blank.

=IF(AND(MONTH(A1)=2,DAY(A1)=28),"2/28","")

Note that the AND & IF are function statements

AND(condition1, condition2)

IF(Condition, True Action,False Action)

All I did was nest the two functions.

Answer 2

Generally speaking most programming languages deal with this by performing the following calculation to ascertain whether this year is a leap year: The percentage symbol means modulo.

LeapYearTest=YYYY % 4

If 2007 % 4 == 0
then this year is a leap year.

If month == 2 and LeapYearTest == 0
then there are 29 days in the month.

Answer 3

Could you use the conditional formatting options to make the text white (or whatever the cell background colour is)?

Answer 4

left click your cell - then press format cells, go onto tab 'number' scroll to dates and choose your selection.