Excel question please

[Nuttapper]

Hi Guys, nice forum just made the jump :)

here's the question:

I have a column (say A1 to A256) full of dates in this format 15/03/2004

what I want to be able to do is get excel to tell me which dates are older than 4 weeks, and return a comment in the cell, like "overdue"

I've read up on the "if" function, but wondered if it could be used as a single command for a cell range rather than each cell at a time.

I would also want the command to grow with the spread sheet, so when I got to cell say, A300 the command would still be working with the current cell (A300)and still with cell A1

Office Xp

Thanks for looking

[Virgin San]

You can set up your "if" statement to evaluate the entire column, rather than just individual cells - so long as you get your if statement right all is well. Or you can write your if statement into 1 cell and simply drag it down - it will be adjusted to apply to other cells.

If you want to mail it over to me i might be able to help you out more easily.

virgin_san@hotmail.com

[Nuttapper]

Hi Virgin Stan thanks for the reply, the sheet is too big to mail. Ive read up on the if function and can make it work on a single cell to return the comment, although I dont know how to make it date relative. For the sheet to work it would have to be aware of todays date so it could count off the 4 weeks and then retun the overdue message.

[Virgin San]

I guess you'll want to use "greater than" or "less than", prob works with dates too. As I said, if you have it in one cell you can drag it down the column to format later cells with the same statement, it will adjust the row number automatically IF you get it set up right :)

[Nuttapper]

yea but how do I make it date aware? I cant really put a fixed date in, its got to be rolling and a coundown to 4 weeks?

[Virgin San]

Good question ...

[Nuttapper]

Thanks for the Help any way Virgin San

[Nuttapper]

Sorted it M8, thanks for the help:

enter the date you put the info in
next column
enter function date & time today
next column
set expiry days (say 4 weeks = 35 days) 35
next column
set date limit by number of days to return a message "overdue"
Hide formula columns leaving the one column Overdue
Excel keeps the current date rolling with the system clock so the gap gets greater between columns 1 and 2 when it gets to the pre determined number of days it flags up the error message "overdue"

Tested and works, reset the system clock to yesterday and then put it back to today. Just needed a restart in excel to make the changes work.

Thanks again Virgin San

Regards

Nuttapper

[Virgin San]

Glad you got it sorted m8 - I'd be interested to know where you live, where I come from 4 weeks is only 28 days :)

[Nuttapper]

Glad you got it sorted m8 - I'd be interested to know where you live, where I come from 4 weeks is only 28 days :)ROFL.... I'm well beat the days just flow into each other at this time of day he he he

:beer: