Practice owner
Links Accountancy
Share this content
0
3
904

Excel help - urgent - pleeease NEW query on this

Hi

I'm really stuck on this, and my laptop doesn't seem to be doing what Excel help pages on the web said it should be???

Anyway, 3 columns below - D, E, and F

Orig spreadsheet just had Issue Date and Response Deadline.  I have been asked to make Response Deadline turn the cell Red or Green depending on whether the date in Response Deadline is more than 10 days after Issue Date, ie day 11 onwards is Red.  So, I added the middle column which calculates the number of days between the issue date and the response (received!!). I am going to 'hide' the middle column when it's done.  I have tried everything I can to make the cells in column F turn Red or Green but I don't seem to be getting a proper menu for my conditional formatting.  I have Windows 7 (which is awful).  I get given a set of pre-written options when I click on Conditional Formatting.

I've been asked to do this as a favour for someone who is under the kosh and needs it by 5.30pm tonight.  Editt: tomorrow am now!

EDIT

I've been playing around with it and think I know what's needed.  Can I do a formula that calculated the number of days from the Issue Date to 'Today'?  If so, I can amend my columns and do conditional formatting for the result of that new formula.

Can anyone help me please???

thanks

Eve

Issue Date   Response Deadline 
30/04/2012 46 15/06/2012
02/05/2012 10 12/05/2012

Replies

Please login or register to join the discussion.

avatar
13th Jun 2012 17:09

Have a look at this

This seems to be just what you need:

http://www.techonthenet.com/excel/questions/cond_format1_2010.php

 

Thanks (0)
avatar
By eve2206
13th Jun 2012 19:29

Thanks.  This was really

Thanks.  This was really useful and did what I wanted - what I thought I wanted; it worked on the elapsed day value.  But I think the spreadsheet is either a flawed design or I need some sort of IF/nested IF statement.  I wanted the cells in F to change background colour when the value was exceeded.  So if the date of, say, 15/6/12 was over the 10 day limit, the date would still show but the cell changes colour.  Is that possible?

I couldn't even calculate the number of days without adding the extra column which I have to hide anyway.  The person who put the sheet together is not very good at Excel and I'm only average really - like the blind leading the blind!

I don't seem to be able to bury the simple formula in E (just a subtraction of F from D to get the number of elapsed days) into the F cells.

The only other way I can think of is to use the column by virtue of its heading - and type in the actual response deadline for each row and apply individual conditional formatting to each cell - which will take ages but doable I guess?

Eve

Thanks (0)
avatar
By eve2206
14th Jun 2012 00:25

Sussed it!!!

 

formula in conditional formatting is (for green)

=(TODAY()-E10)<1

 

and

=(TODAY()-E10)>=1 (for red)

I needed to measure the deadline (target date) against today's date.

At least I think that's right!

Eve

Thanks (0)