Excel help please

MikeHunt79 · 7 May 2009 at 10:21

I'm trying to compare months from dates in a spreadsheet, and blank out a column if it is a different month to other columns.

Is there a way to do this with Conditional Formatting?

Jono · 7 May 2009 at 10:46

I'm not answering this, but instead I'll helpfully hijack this thread, sort of related I guess...

I have dates in two columns. I need to do a couple of things:

Compare DateA to Today() - if it's <= 90 (days) colour DateA Red, or <= 30 colour it Orange.
Compare DateB with DateA. - if it's <= 30 (again, days) colour DateB Red, <= 15 colour it Yellow, otherwise Green.

I've had a bit of a play with the conditional formatting thing, but I'm clearly stupid and can't get it to work properly. It seems to colour everything in the column red/orange/etc.

Not quite sure what the forumla should be. Or is it easier to do this with VBA? Not that I can remember how that works properly either.

Cheers,

Gornall · 7 May 2009 at 10:56

Jono said:
I'm not answering this, but instead I'll helpfully hijack this thread, sort of related I guess...

I have dates in two columns. I need to do a couple of things:

Compare DateA to Today() - if it's <= 90 (days) colour DateA Red, or <= 30 colour it Orange.

Compare DateB with DateA. - if it's <= 30 (again, days) colour DateB Red, <= 15 colour it Yellow, otherwise Green.

I've had a bit of a play with the conditional formatting thing, but I'm clearly stupid and can't get it to work properly. It seems to colour everything in the column red/orange/etc.

Not quite sure what the forumla should be. Or is it easier to do this with VBA? Not that I can remember how that works properly either.

Cheers,

Without getting my hands on Excel it might be best to do it the other way round.

so >= 10 Yellow >=20 Red etc.

Remember 20 <30 but its also less than 60 70 80 90 so its probably just checking the first parameter. Is it less than 90? Yes, ok colour it red and move to the next cell.

Gornall · 7 May 2009 at 11:04

Got on excel at work.

If you can understand what I was saying, it should work that way.

Jono · 7 May 2009 at 11:30

Gornall said:
Got on excel at work.

If you can understand what I was saying, it should work that way.

I'm very tired and brain not work, could you paste the formula you used please?

Gornall · 7 May 2009 at 13:28

Jono said:
I'm very tired and brain not work, could you paste the formula you used please?

Its a bit hard as I just tested my theory on blank sheet then closed it.

Basically. Have a cell with the =Today()

DateA DateB
date
date
date
date

Then your Conditional formatting would be something like.

If "date" greater than "=Today()" cell minus 90 Format RED

If "date" greater than "=today()" cell minus 30 Format Yellow.

If its not greater than 90 days it will then check if its greater than 30.

Hope that makes sense. I tested it a little, like I say! I'm at work so can't be as thorough as I like

Ack, tried redoing it and I've hit the wall as well. Trying to piece together what I did.

Its basically and IF/Else statement your after.

Gornall · 7 May 2009 at 14:00

=IF(A3>($C$1-$E$2),"Greater than Ninety",IF(A3>($C$1-$E$3),"Greater Than 30","Fine"))

If you can decipher that, thats how I would do it.

E2 and E3 been your 90 and 30 reference (so if you ever change them)

A column is your DateA and C1 is your =today() field.

Basically if its over 90, say over ninety, else if its over 30 print its over else say fine.

MikeHunt79 · 7 May 2009 at 14:15

Cheers for the help, I think I've figured out what the problem is... For some reason Excel assumes every month has 31 days, is there any was of getting around this with some formula's or code?

Gornall · 7 May 2009 at 14:24

MikeHunt79 said:
Cheers for the help, I think I've figured out what the problem is... For some reason Excel assumes every month has 31 days, is there any was of getting around this with some formula's or code?

Surely setting the cell format to date should correct that.

Taking a number away from a date field counts the days back as far as I know.

EffBee · 7 May 2009 at 17:47

MikeHunt79 said:
I'm trying to compare months from dates in a spreadsheet, and blank out a column if it is a different month to other columns.

Is there a way to do this with Conditional Formatting?

Just a thought - dates in excel are stored as a number and only displayed as a date format.

With a bit of experimentation you could work out what numbers correspond to the months you are interested in and make some rather complicated and long formuale comparing numbers and doing things depending...

MikeHunt79 · 7 May 2009 at 18:12

Well, I sort of gave up in the end and just formatted them by hand... Cheers for the help tho, Excel can be amazing at times but frustrating at others...