Solved How can I use the IF function in Excel for a library system?

June 23, 2015 at 22:41:26
Specs: Windows 7
I want that the due date will turn to red whenever it is due or there will be a notification that's it's already due.

See More: How can I use the IF function in Excel for a library system?

Report •

✔ Best Answer
June 25, 2015 at 08:00:47
To answer your specific question,
Can my name be highlighted with whatever color if today is June 28, 2015?

What your looking for the the Past Due Dates How-To.

With your data like:

     A           B     
1) Name     Date Book Out  
2) Ruben     06/22/2015 

1) Select your cell, A2 (For only the name highlighted)
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =TODAY()-B2>=3

6) Click on the Format button
7) Select the Fill Tab
8) Select a color
9) Click OK
10) Click OK

You specified 3 days, notice in the formula, the last digit is a 3, meaning 3 days.

So using the Date Out and Todays Date (06/25/2015) the name in cell A2 should change color.
Change the Date in B2 to 06/23/2015 and the name is no longer highlighted.

That should answer your specific question.

But, I'm not a big fan of re-inventing the wheel, so unless you have a spreadsheet already created, you might want to look at this site:

http://myexceltemplates.com/library...

It has a free spreadsheet already created and it might be what your looking for.

But, if you want to create your own spreadsheet or you already have one created and you want to enhance its capabilities, then continue to ask questions and we'll be glad to help.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy



#1
June 24, 2015 at 06:06:43
Without more info it would be difficult to give a precise answer,
but you might want to look at these two HOW-TO's and see
if either gives you what your looking for.

Using Deadline Dates:

http://www.computing.net/howtos/sho...

Using Past Due Dates:

http://www.computing.net/howtos/sho...

Any questions, please ask.

MIKE

http://www.skeptic.com/


Report •

#2
June 24, 2015 at 23:30:23
Thank you very much for your response but I've tried it and I can't make it right. If it's okay with you, can you explain it further- the mechanics on how will it work? I'm hoping for your understanding and kind response.

Report •

#3
June 24, 2015 at 23:55:53
Let me ask another question. For example, I borrowed a book today (June 25, 2015) and it should be returned on June 28, 2015 which is three days after I borrowed it. Can my name be highlighted with whatever color if today is June 28, 2015? Is it possible to do that? I want that if today is the due date for my book, my name should be highlighted. Thanks!

Report •

Related Solutions

#4
June 25, 2015 at 05:47:54
Before we get too far into this process,
let me ask if you already have a spreadsheet designed?

If you do, please post a short example,
using both Column Letters and Row Numbers
That way the formulas will be correct for your sheet and should not need to be changed.

If you do not have a spreadsheet already designed,
what do you want it to look like?
What information should it contain, aside from Name, Date Out, and Date In?

MIKE

http://www.skeptic.com/


Report •

#5
June 25, 2015 at 08:00:47
✔ Best Answer
To answer your specific question,
Can my name be highlighted with whatever color if today is June 28, 2015?

What your looking for the the Past Due Dates How-To.

With your data like:

     A           B     
1) Name     Date Book Out  
2) Ruben     06/22/2015 

1) Select your cell, A2 (For only the name highlighted)
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =TODAY()-B2>=3

6) Click on the Format button
7) Select the Fill Tab
8) Select a color
9) Click OK
10) Click OK

You specified 3 days, notice in the formula, the last digit is a 3, meaning 3 days.

So using the Date Out and Todays Date (06/25/2015) the name in cell A2 should change color.
Change the Date in B2 to 06/23/2015 and the name is no longer highlighted.

That should answer your specific question.

But, I'm not a big fan of re-inventing the wheel, so unless you have a spreadsheet already created, you might want to look at this site:

http://myexceltemplates.com/library...

It has a free spreadsheet already created and it might be what your looking for.

But, if you want to create your own spreadsheet or you already have one created and you want to enhance its capabilities, then continue to ask questions and we'll be glad to help.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#6
June 25, 2015 at 17:11:08
Can I ask your email for me to send the design I want? If it's okay with you because I don't know how to insert my file in this discussion box

Report •

#7
June 25, 2015 at 17:19:33
Thank you very much for your answer. I understand now!

Report •

Ask Question