Windows 7 Forums is the largest help and support community, providing friendly help and advice for Microsoft Windows 7 Computers such as Dell, HP, Acer, Asus or a custom build. I need some help with excel I run a library with the help of an excel sheet.
The current document is not going to work and here is why:
You return date formula is based on the function 'Today'.
Let's say a student takes a book today for 21 days, when you open this file tomorrow, the return day would be recalculated for 21 days from tomorrow, so those days would be always shifting in time - always 21 days ahead, no matter when you open the documents.
So here is what I propose:
Make one more column: Date Taken (Start date) to write the date when an item was taken.
Reduce Return Date column to one and use several enclosed IF functions, which would depend on item selected in drop down menu.
Because school is closed on weekends, a student is not able to return an item, so we would use 'business days' instead calendar days. Forumula =WORKDAY(StartDate,Days,Holidays) works like a charm here if we use only first part =WORKDAY(StartDate,Days).
If you want to use calendar days you can simply use StartDate+N, where N is number of days for book, dvd, cd correspondingly.
Check out attachment - I have implemented all of that in there
(with business days, not calendar days).
You return date formula is based on the function 'Today'.
Let's say a student takes a book today for 21 days, when you open this file tomorrow, the return day would be recalculated for 21 days from tomorrow, so those days would be always shifting in time - always 21 days ahead, no matter when you open the documents.
So here is what I propose:
Make one more column: Date Taken (Start date) to write the date when an item was taken.
Reduce Return Date column to one and use several enclosed IF functions, which would depend on item selected in drop down menu.
Because school is closed on weekends, a student is not able to return an item, so we would use 'business days' instead calendar days. Forumula =WORKDAY(StartDate,Days,Holidays) works like a charm here if we use only first part =WORKDAY(StartDate,Days).
If you want to use calendar days you can simply use StartDate+N, where N is number of days for book, dvd, cd correspondingly.
Check out attachment - I have implemented all of that in there
(with business days, not calendar days).