Working with Dates

  • Thread starter Thread starter matt.albery
  • Start date Start date
M

matt.albery

One column in my spreadsheet contains dates in the format mm/dd/yyyy.
Many of the cells in this column are blank, but for those that contain
a date, I want to determine which ones come after 05/13/2007.

I have created a column next to the date column, in which I hoped to
put an X in each cell that neighbors a date after 5/13/2007. I have
tried to use the formula
=IF(AND(cell>=DATE(2007,5,13),cell<=DATE(2007,7,5)),"X","O") ... As
this has worked for me in the past. In this case, though, it is
putting an O in every cell regardless of what the date is.

Is there a better formula that I can use?

Might there be a reason that this worked on other spreadsheets, but
not on this one?
 
Assuming that your "cell" is actually a cell reference then your formula
works for me. It may be that you "Dates" are actually Text representation
of dates.

Try =ISTEXT(A1) for a cell with a "Date"

If the "Dates" are really text thn copy an empty cell and highlight the
range of cells ans then select Paste Special > add and then change the
formatting to your desired date format

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
One column in my spreadsheet contains dates in the format mm/dd/yyyy.
Many of the cells in this column are blank, but for those that contain
a date, I want to determine which ones come after 05/13/2007.

I have created a column next to the date column, in which I hoped to
put an X in each cell that neighbors a date after 5/13/2007. I have
tried to use the formula
=IF(AND(cell>=DATE(2007,5,13),cell<=DATE(2007,7,5)),"X","O") ... As
this has worked for me in the past. In this case, though, it is
putting an O in every cell regardless of what the date is.

Is there a better formula that I can use?

Might there be a reason that this worked on other spreadsheets, but
not on this one?
Click to expand...


P the folllowing formula cell B1 and copy it to the bottom of you
worksheet.
=IF(ISBLANK(A1),"",IF(A1>05/07/2007,"X","")

Better yet conditional Format column A =A1>05/07/2007 andformat to
change color.

ed
 
Back
Top