Re: Library Date Calculation
It doesn't seem to work in the other cells in my one:confused:
Edit: No its fine, sorry
Re: Library Date Calculation
It doesn't seem to work in the other cells in my one:confused:
Edit: No its fine, sorry
Re: Library Date Calculation
Quote from ByTheCringe2New formula for J16, filled down:
=IF(OR(H16="",H16>=TODAY()),"",(TODAY()-H16)*1)
Thanks a lot : D .
But how come I cannot enter a date above the 12th for any month
Re: Library Date Calculation
Thanks , True it is steep but just want to see but when I drag the formula down I get prices on borrowed books.
Attachment of latest:
Re: Library Date Calculation
Quote from ByTheCringe2You still have the old conditional formatting. It needs to be changed to:
Cell Value is: Equal to: "Borrowed" Yellow
Cell Value is: Equal to: "Shelf" Green
Cell Value is: Equal to: "Overdue" Red
Thanks: D
But how do you do a formula in the new column named "fine" to add £1 for each day the book is overdue?
Edit: Also just noticed when I enter a date in the "Date taken out" column it doesn't take a date over the 12th of any month or of any month of the year 2006
Re: Library Date Calculation
Just restarted everything works excepts the conditional formatting.
Here's the attachment:
Re: Library Date Calculation
Quote from ByTheCringe2Display MoreHello, Hello. (!) Here are some ideas:
I am not sure what things happen when books are taken out or returned. Assuming G16 is blank if a book is on the shelf, and the date is entered when it is borrowed, then put this formula in H16 and fill down:
=IF(G16="","",G16+21) where 21 is the allowed borrowing time.
Then in cell F16 and filled down, put this formula:
=IF(H16="","Shelf",IF(H16>=TODAY(),"Borrowed","Overdue"))
Then the conditional formatting for the cells F16 down can be
Cell Value is: Equal to: "Borrowed" Yellow
Cell Value is: Equal to: "Shelf" Green
Cell Value is: Equal to: "Overdue" Red
Thanks but the conditional formatting didn't work:
"Borrowed" is yellow (which is fine)
"Shelf" is red
"Overdue" is yellow
The dates do work fine, but is the "status" supposed to change to overdue if the dates passes?
Re: To Get Fines To Automatically Update
Quote from Arthur494Hi!
Yes, the formula you need is TODAY(). This gives today's date. So you can calculate the number of days since the book was taken out with "=TODAY()-B12".
If the reader is allowed 21 days: "=MAX(TODAY()-B12-21,0)" gives the number of days overdue.
and welcome to Ozgrid!
Thanks but
I am very dense at excel as the next question will show, but what does "-B12" mean? and also when I do this code: "=MAX(TODAY()-B12-21,0)" it goes from 1/20/2007 to 12/30/2007.
Sample workbook (hope it works):
I am trying to create a system for a library but I don't know how to get the fines to automatically update day by day when a book is overdue.
Is there a formula for this?
Please help