mads
|
| posted on 15/3/12 at 01:16 PM |
|
|
having dumb moment with excel (date checking and conditional formatting)
Afternoon all,
I am having a bit of a dumb moment with Excel.. I can't seem to think of a way to do what I need to, can someone please help?
Basically I have two worksheets in one workbook. One is called summary and the other is called data input.
Summary Worksheet
Column A - has a list of areas
Column B - titled "Done in last 12 months"
Column C - titled "Next due by"
Data Input Worksheet
Column A - has a list of areas
Column B - titled 2012
Column C - titled 2011
Column D - titled 2010
etc etc
Basically, users input the date an audit has been completed in the respective year column against the area.
What I am trying to work out is the best way to do the following:
Column B [Summary] - to show yes/no (maybe some visual conditional formatting too) depending if there is an appropriate date in the year columns [Data
Input] that is less than 365 days from todays date
Column C [Summary] - show when the next due date is (again maybe with some visual conditional formatting thrown in e.g. if well overdue or if coming
up)
The part I am mainly struggling with is how to search the columns and get Column B [Summary] to show if there was a date within the last 12 months.
Plus, the fact that this will grow over the years so each year Column B [Data Input] will be the current year.
Anyone have any suggestions on how to tackle this?
Thanking you in advance 
mads
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
|
|
pekwah1
|
| posted on 15/3/12 at 02:06 PM |
|
|
Have a cell which displays "=MAX(B:B)" for the dates you want to search.
This will bring up the latest date.
Then use this on that cell:
=IF(C5>NOW()-365, TRUE, FALSE)
That will compare the last date in the list again today's date minus 365 days.
|
|
|
|
