Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Date formula

Posted on 2015-02-03
14
Medium Priority
?
71 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:Pete Edwards
  • 7
  • 6
14 Comments
 
LVL 31

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:Pete Edwards
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
Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

 

Author Comment

by:Pete Edwards
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 31

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:Pete Edwards
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 31

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
 
LVL 31

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:Pete Edwards
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 31

Expert Comment

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

Author Comment

by:Pete Edwards
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 31

Accepted Solution

by:
gowflow earned 2000 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:Pete Edwards
ID: 40586923
gowflow..... U DA MAN!!!!!  Feel kinda retarded to what my problem was, but I got it now!!  

Thanks
0
 
LVL 31

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn the different options available in the Backstage view in Excel 2013.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.

963 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