Excel, my bane, my best friend.

February 2, 2008 8:26 PM

Can I program Excel to read a date in a cell, and when that date has passed, make Excel alert me by highlighting the cell or something along those lines?

I am trying to create a simple spreadsheet in Excel to track overdue books at my library. (No, we don't have a catalog with which to do this, and no, my IT guy won't let us install Koha, or any other catalog for that matter). Anyway, what I want to do is just have a row for each book that is checked out, with one cell in the row displaying the item's due date. When that date passes, I would like the cell (or, even better, the entire row) to change colors so I know that the book is overdue. It seems like this should be fairly easy, but what do I know? Can anyone help me out?

posted by foxinthesnow to technology (8 comments total)

What you want is conditional formatting. If you have the 2007 version, I believe you can just right-click on the cell in question. In 2003, go to Format > Conditional Formatting. Tell it to highlight the cell if the cell value in question is greater than =TODAY().
posted by desjardins at 8:36 PM on February 2, 2008

I meant less than =TODAY()
posted by desjardins at 8:38 PM on February 2, 2008

What desjardins said. And of course if you don't want to compare the date to today (say you want to compare it to some other date), you put the other date in some other cell -- doesn't matter where -- and then compare to that when you do the conditional format.
posted by Kadin2048 at 8:55 PM on February 2, 2008

See yesterday.
posted by dmd at 9:21 PM on February 2, 2008

and no, my IT guy won't let us install Koha, or any other catalog for that matter

is this not your actual problem? you're obviously aware that using excel as a database isn't a real solution, and your IT guy is there to support the function of your organisation, not to dictate what you shall and shall not do. if all he does is say "no you can't" without providing reasons or alternatives, perhaps you need a new IT guy...

sorry, not an actual answer, I know, but I've dealt with enough shambling horror "database" systems that evolved from exactly this kind of start...
posted by russm at 9:33 PM on February 2, 2008

If you have MS Office, you might be better off doing an Access database. Then you could query your list to get just those books that are overdue.

If you want to keep it simple and in Excel, I would actually use an auto-filter. Just do "Custom" and select less than or equal to, then put todays date in the value field.

The advantage here is, you could print it out to give to the IT guy and make him call them all for a reminder. :) Hey, if he won't let you have software you need, give him some real work to do.
posted by slavlin at 9:49 PM on February 2, 2008

Personally, I would use Google Calendar to send reminders to my gmail account, or do the same with Outlook.

I'm not sure where you are in Michigan, but Library Elf might work for you - it hooks into your library account and adds a bunch of functionality your library might not offer - like email reminders and such. Here's their library list.
posted by O9scar at 11:38 PM on February 2, 2008

slavlin has a better suggestion than I did. Autofilter is exactly what you want to use, if you have to use Excel.
posted by desjardins at 7:29 AM on February 3, 2008