Excel, my bane, my best friend.
February 2, 2008 8:26 PM Subscribe
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?
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?
I meant less than =TODAY()
posted by desjardins at 8:38 PM on February 2, 2008
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
posted by Kadin2048 at 8:55 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
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
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
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
posted by desjardins at 7:29 AM on February 3, 2008
« Older A gift for a new member of the family? | Women in the service industry vs Chauvinist pigs Newer »
This thread is closed to new comments.
posted by desjardins at 8:36 PM on February 2, 2008