Solved

Date formula

Posted on 2015-02-03
14
61 Views
Last Modified: 2015-02-03
I would like instructions if capable to insert a formula(I believe maybe through data validation) to whereas when a date within a column has aged over 15 days, the "cell" itself changes to a highlighted color.

Is this possible with excel?
Aged-Date.xlsx
0
Comment
Question by:pedwards3
  • 7
  • 6
14 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40586378
within a column has aged over 15 days

compared to what column's date ? in here you should have 2 dates. Your sheet attached has only 1 column date.

This is feasible in conditional formatting not data validation.
gowflow
0
 

Author Comment

by:pedwards3
ID: 40586392
ok, wasn't sure what the name, honestly haven't played around much with "conditional", or "data validation".  Can you apply a conditional format whereas when the date of the day you open the spreadsheet has breached a "timeline" of days(you can choose) the cell turns a highlighted color.  Also, can you please provide "bulleted" instructions for me to repeat and learn for myself please.
0
 
LVL 19

Expert Comment

by:Iammontoya
ID: 40586400
Here's a very easy tutorial. Please let me know if you have any questions.

http://www.excel-easy.com/data-analysis/conditional-formatting.html
0
 

Author Comment

by:pedwards3
ID: 40586442
gave it a whirl, still don't know the formula to apply whereas it looks at a cell range and the current date and highlights the cells if it is say older than 5 days, look at what I tried, and didn't work.  I don't know how to ask the logic of todays date and a range older than a certain days, can you help?
Aged-Date.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40586512
Well I don't understand your request.

In col A you have
1/2/2015
1/3/2015
1/8/2015


You want 15 days from what date ? Like is the date in A is 15 days from today ? or what this I don't understand your 15 days compares to what ?

gowlfow
0
 

Author Comment

by:pedwards3
ID: 40586520
today's date is Feb 3rd, you can change these dates you see now, but I want a I believe "if" formula written whereas if the date in the cell is "older" than say 31 days for example from the date of the current day, the cell will turn a highlighted color.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40586529
So now it is no more 15 days it is 31 ???
Make up your mind so I can write the formula

gowflow
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 29

Expert Comment

by:gowflow
ID: 40586556
Here it is select entire Col A then choose Conditional formatting in the first menu then select Manage then add new rule then choose formula then insert this formula
=AND(A1+15<TODAY(),A1<>"")
and choose the filling color press ok and exist.

I made it in the attach workbook. it is set for 15 days you can modify and change to 30 31 etc..
gowflow
Aged-Date.xlsx
0
 

Author Comment

by:pedwards3
ID: 40586607
thanks, look like it worked for you.  I applied it to this file, column "m" and it did not work for me, do you know whe?
Inventory-Valuation---WIP.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40586635
Here u go
gowflow
Inventory-Valuation---WIP.xlsx
0
 

Author Comment

by:pedwards3
ID: 40586840
gowflow, i am going to apply this methodology going forward, I can save your formula, and modify "days" accordingly.  Why did mine not work, formatting??  I don't want to put out a question each and every time I try to use the method, and it craps out for me.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40586882
The formula I posted first was:
=AND(A1+15<TODAY(),A1<>"")

this means:
if value in Col A for each cell +15 is less than today's date and value of that cell is not blank then color it !!!

This worked in the sample workbook that I posted that had the values in Col A.

You applied this same formula in your workbook but your values were in Col M so you should had modified this formula to be:
=AND(M1+15<TODAY(),M1<>"")

however you kept it referring to Col A
=AND(A1+15<TODAY(),A1<>"")

This is why it did not work !!! :)

gowflow
0
 

Author Closing Comment

by:pedwards3
ID: 40586923
gowflow..... U DA MAN!!!!!  Feel kinda retarded to what my problem was, but I got it now!!  

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40586934
No problem we are all retarded somehow in our own weaknesses so don't worry we are here to help. Pls feel free to ask for any help you may need by posting a link in here for any new question and will be glad to assist you.

gowflow
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
Viewers will learn how to create a PivotTable and make basic changes to it in Excel 2013.
Video by: Zack
Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now