Excel and dates

March 21, 2011 at 06:57:22
Specs: Windows Vista
I have two columns of dates, A and B. Column B is just As date plus 30 day. What i want it to do is, whenever i update column A's date, i dont want column B's date to change. how do i do that??

See More: Excel and dates

Report •


#1
March 21, 2011 at 12:49:41
i dont want column B's date to change.

Remove the formula that updates B?

MIKE

http://www.skeptic.com/


Report •

#2
March 22, 2011 at 05:21:13
haha thanks yea, i was figuring i was going to have to do that, but i didnt want to enter in over 200 lines of data which sucks. i also have another question if you dont mind. I have for columns of data which are dates. A, B C and D.

A (inspected date)

B (30days after inspected date)

C (60days after inspected date)

D (90 days after inspected date)

what i am trying to do but cannot figure is to conditionally format B,C and D. As in all dates <30 days after are green, 30days after - 60 days is yellow, 60 days - 90 days would turn orange, then 90 + days is red. Is that possible? or is that to many conditions. Im using Excel 2007 if that makes a difference.


Report •

#3
March 22, 2011 at 11:54:19
Didn't we just do that dance?

http://www.computing.net/answers/of...

Just modify the formulas.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
March 22, 2011 at 11:55:25
i didnt want to enter in over 200 lines of data which sucks.

If you just want to keep the data that is already in those cell then do:

Copy
Past Special
Values

MIKE

http://www.skeptic.com/


Report •

#5
March 22, 2011 at 12:03:30
ok nvm. . thanks anyways

Report •

#6
March 22, 2011 at 12:20:21
Maybe I'm missing something here,
post an example of your spreadsheet
and explain when and how all of the various dates are being used.

The only date I'm sure of is column A the inspected date
All your other columns are a mystery.

MIKE

http://www.skeptic.com/


Report •

#7
March 22, 2011 at 12:32:05
Inspected	     30 Days after	 60 Days after        90 Days after
        
         27-Aug-10	     26-Oct-10	26-Oct-10	             25-Nov-10
         21-Aug-09	     20-Oct-09	20-Oct-09	             19-Nov-09
          2-Sep-09	      1-Nov-09	1-Nov-09	              1-Dec-09
          23-Sep-09	       22-Nov-09	22-Nov-09	              22-Dec-09

all days 29 days and more BEFORE the 30 day after should be green, 30 days after to 60 should be yellow, 60 days to 90 days turn orange, and 90+ days after change red. this all happens when the inspected date is updated by the user.

right now i have red = today()+90 between today()+1000
orange - today()+60 between today()+89
yellow - today()+29 between today()+59
green - today()<=29

but i think ihave it backwards or something because the wrong #s are not changing the right colors and i cant figure out how to make it work right.


Report •

#8
March 22, 2011 at 12:43:23
How are all of these dates being entered?

Are you sure they are all dates?
To check, select a range of dates and Format as Number.
If you see a five digit number then it's a date,
if it does not change, it's formatted as TEXT.

Post your formulas.

MIKE

http://www.skeptic.com/


Report •

#9
March 22, 2011 at 12:52:03
pretty much the inspected date is entered in manually as each inspection is done. column C is just B+30
column D is B+60
column E is B+90

as the days go by from today, and C,D and E get within their parameters of column B(inspected date) its just being done so we all know that when one of the columns turns yellow we need to scedule a inspection, and when it gets to red then we have to pretty much scramble to do an inspection or get extension paperwork for it.


Report •

#10
March 22, 2011 at 13:02:44
Still do not understand how the sheet is used.

You have:

       A               B            C             D
1) Inspected   30 Days after   60 Days after   90 Days after
2)
3) 27-Aug-2010	26-Oct-2010	26-Oct-2010	25-Nov-2010

It was first inspected on 27 Aug 2010 (cell A3)
but in the 30 Day After column (cell B3) you have the date 26 Oct 2010 which is 60 days since first inspected.

What happened to the 30 day date?

What is the relationship of each date to column A and/or each other?

MIKE

http://www.skeptic.com/


Report •

#11
March 22, 2011 at 13:16:32
Let's see if this is correct-

You manually enter the date of first inspection in cell A3

You then have a formula calculate and enter the next inspection date, 30 day from A1

You then have a formula calculate and enter the next inspection date, 60 day from A1

You then have a formula calculate and enter the next inspection date, 90 day from A1

Am I right so far?

MIKE

http://www.skeptic.com/


Report •

#12
March 22, 2011 at 19:26:58
yea your absolutely correct with how that works. and about your question bout that one odd date, it was a typo im sorry for the confusion. pretty much i just want 30, 60 and 90 columns to change color as Todays() date gets closer to the inspection date. and then when someone actually goes out and does an inspection and updates column A it all should go back to green.

Report •

#13
March 23, 2011 at 08:01:30
OK,

A few more questions.

You enter todays date in cell A3 = 23-Mar-2011

The spreadsheet automatically updates cell B3 with the date 30 days from the date in cell A3 so B3 = 22-Apr-2011

The spreadsheet automatically updates cell C3 with the date 60 days from the date in cell A3 so C3 = 22-May-2011

The spreadsheet automatically updates cell D3 with the date 90 days from the date in cell A3 so D3 = 21-Jun-2011


If you want cell B3 to turn green within 30 days, then the day you enter the date in cell A3, by default, cell B3 will be 30 days before the date in cell A3,
so B3 will turn green.

If you want cell C3 to turn yellow within 60 days from the day you enter the date in cell A3, by default, cell C3 will be 60 days before the date in cell A3,
so cell C3 will turn yellow.

See the problem?

Now if you want the date in cell B3 = 22-Apr-2011
to turn green 2 days before so that when TODAY() = 20-Apr-2011
or 5 days before so that when TODAY() = 17-Apr-2011
or 10 days before so the when TODAY() = 12-Apr-2011

that would work.


MIKE

http://www.skeptic.com/


Report •

#14
March 23, 2011 at 12:21:43
Yea i understand what your saying. . . its just my boss and how he wants things. The inspected date wont change unless manually entered. They just wanted the warnings for when they neared 30, 60, and 90 to appear as todays date gets close or past the inspected date. and when the team goes out for the inspection and it passes. they come back, update the inspected date to where its current, then the 30, 60 and 90 day will all go back to green.Does that make any sense?

Report •

#15
March 23, 2011 at 17:46:50
OK, try this, how about we have the whole row light up.


       A           B           C           D
1) Inspected   30 Days     60 Days     90 Days 
2) 			
3) 23-Mar-11   22-Apr-11   22-May-11   21-Jun-11
4) 18-Feb-11   20-Mar-11   19-Apr-11   19-May-11
5) 21-Jan-11   20-Feb-11   22-Mar-11   21-Apr-11
6) 22-Dec-10   21-Jan-11   20-Feb-11   22-Mar-11

We are going to ignore all of the dates/columns except those in column A the date of inspection.

There are now FIVE separate formulas, and they must be in the correct order,
so after your done entering all the formulas be sure they are in the correct order:

GREEN
YELLOW
ORANGE
RED
No Color

To get the rows to change colors, first select your range.
In my example that would be A3 through D6, modify to suit your conditions

To get the formulas in the correct order, we enter them from bottom (No color)
to top (Green)


First Formula:

1) On the ribbon click Conditional Formatting

2) Click on New Rules, it’s near the bottom of the dialog box.

3) Click Use Formula to determine which cells to format.

4) Enter the formula: =IF($A3="",TRUE,FALSE)
-- Note the dollar sign next to just the A

5) Click on the Format button

6) Select the Fill Tab

7) Select "No Color"

8) Click OK

9) Click OK
----------------
Second Formula:

Same as 1 - 3 above:

4) Enter the formula: =IF(AND($A3<>"",DATEDIF($A3,TODAY(),"d")>90),TRUE,FALSE)
-- Note the dollar sign next to just the A

Same as 5 - 6 above

7) Select RED color
----------------
Third Formula:

Same as 1 - 3 above:

4) Enter the formula: =IF(DATEDIF($A3,TODAY(),"d")<=90,TRUE,FALSE)

Same as 5 - 6 above

7) Select ORANGE color
----------------
Fourth Formula:

Same as 1 - 3 above:

4) Enter the formula: =IF(DATEDIF($A3,TODAY(),"d")<=60,TRUE,FALSE)

Same as 5 - 6 above

7) Select YELLOW color
----------------
Fifth Formula

Same as 1 - 3 above:

4) Enter the formula: =IF(DATEDIF($A3,TODAY(),"d")<=30,TRUE,FALSE)

Same as 5 - 6 above

7) Select GREEN color

----------------

What you should get is:

When column A has a date: the row will be Green from day 1 thru day 30
When column A has a date: the row will be Yellow from day 31 thru day 60
When column A has a date: the row will be Orange from day 61 thru day 90
When column A has a date: the row will be Red from 91 days and greater.
When column A is blank, the row will be blank


See how that works out.

MIKE

http://www.skeptic.com/


Report •

#16
March 24, 2011 at 05:18:21
Those formulas look like they should work good, but for some reason none of my cells are turning any color, do you have any ideas why, is it cause all of my stop if true boxes are checked off or something else?

Report •

#17
March 24, 2011 at 12:02:59
Un-check all of the "Stop If True" box's.

Make sure all your column A cells are really DATEs not TEXT that look like dates.

When you entered the formulas did you do a "Copy & Paste" from the forums or did you enter them freehand?
If you entered freehand, double check to make sure they are the same as shown.

Try downloading my example in reply # 15 and put them in a new blank spreadsheet and see if you can get them working.

In my example above,
Row 1 should be Green
Row 2 should be Yellow
Row 3 should be Orange
Row 4 should be Red

MIKE

http://www.skeptic.com/


Report •

#18
March 24, 2011 at 12:19:27
ok i put them in exactly. . and they work i appreciate everything Mike.

Report •

#19
March 24, 2011 at 12:44:07
Glad you finally got everything working.

MIKE

http://www.skeptic.com/


Report •

#20
March 25, 2011 at 09:25:09
Mike, great patience with Ryan to help him to resolve his issue.

Can you help me with my issue? What is the process/procedure - do I start a new post?

Thanks,

Hank


Report •

#21
March 25, 2011 at 12:07:06
hmoorex3

Yes, just start a new "thread" make sure the title reflects what your problem is.

BUT FIRST

Read these two How-To's, it will make everyones life a bit easier:

1) http://www.computing.net/howtos/sho...

2) http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

#22
June 6, 2011 at 08:49:50
Hey guys,
I am desperately trying to figure out the number of days from 2 days that might fall between April 1, 2011 and September 31, 2011. I came up with this formula but it's not working.

=DATEDIF((A3>31-MAR-2011),(B3<1-OCT-2011),("D"))
What am I doing wrong?


Report •

#23
June 6, 2011 at 09:27:50
re: "What am I doing wrong?"

What you are doing wrong is posting a new question in an old thread.

Please start a new thread with a relevant subject line.

Thanks!

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question