Solved

Date formula

Posted on 2015-02-03
14
62 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:Montoya
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We need a new way to communicate time sensitive or critical info.   The best part of my role at xMatters is visiting our clients all over the world to learn about how they operate their businesses, share insights that xMatters has gleaned across…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn the different options available in the Backstage view in Excel 2013.
Viewers will learn how to create a PivotTable and make basic changes to it in Excel 2013.

914 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

14 Experts available now in Live!

Get 1:1 Help Now