Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • Last Modified:

Date formula

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
Pete Edwards
Asked:
Pete Edwards
  • 7
  • 6
1 Solution
 
gowflowCommented:
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
 
Pete EdwardsAuthor Commented:
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
 
MontoyaProcess Improvement MgrCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Pete EdwardsAuthor Commented:
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
 
gowflowCommented:
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
 
Pete EdwardsAuthor Commented:
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
 
gowflowCommented:
So now it is no more 15 days it is 31 ???
Make up your mind so I can write the formula

gowflow
0
 
gowflowCommented:
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
 
Pete EdwardsAuthor Commented:
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
 
gowflowCommented:
Here u go
gowflow
Inventory-Valuation---WIP.xlsx
0
 
Pete EdwardsAuthor Commented:
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
 
gowflowCommented:
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
 
Pete EdwardsAuthor Commented:
gowflow..... U DA MAN!!!!!  Feel kinda retarded to what my problem was, but I got it now!!  

Thanks
0
 
gowflowCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now