Excel Spreadsheet help pls.

[hamster]

As simply as I can put it, this is my dilemma.

 

Currently

I have a list of people's names in column 'A'.

 

I have a list of the dates in column 'B'. For arguments sake assume that it is their birthdays in this column.

 

Objective

I need column 'C' I need the date to be exactly 1 year from the date in column 'B'

 

I need this data (column 'C') to be replicated for each person (ie. each 'Row') bearing in mind that the dates in column 'B' vary of course.

 

The really hard bit

I need cell in column 'C' to change colour when the date is approaching (ie. at eleven months). I think that this bit is called 'conditional formatting'.

 

If anyone can help it would not only save me a real headache but also get me one zillion brownie points.

 

It may help to know that this spreadsheet is for people's annual review dates but for obvious reasons I can not divulge more than that.

 

Cheers in advance.

 

hamster

PS

Maybe one of the resident teachers might know a pupil who can resolve this one?? ;-)

[Johnny Bognor]

As simply as I can put it, this is my dilemma.

 

Currently

I have a list of people's names in column 'A'.

 

I have a list of the dates in column 'B'. For arguments sake assume that it is their birthdays in this column.

 

Objective

I need column 'C' I need the date to be exactly 1 year from the date in column 'B'

 

I need this data (column 'C') to be replicated for each person (ie. each 'Row') bearing in mind that the dates in column 'B' vary of course.

 

 

The really hard bit

 

I need cell in column 'C' to change colour when the date is approaching (ie. at eleven months). I think that this bit is called 'conditional formatting'.

 

If anyone can help it would not only save me a real headache but also get me one zillion brownie points.

 

It may help to know that this spreadsheet is for people's annual review dates but for obvious reasons I can not divulge more than that.

 

Cheers in advance.

 

hamster

PS

Maybe one of the resident teachers might know a pupil who can resolve this one?? ;-)

 

If the name is "Hamster" in column A and "25/01/2010" is in column B, then you need to add 365 days to column B.

 

Therefore in column C, you should have "=B1+365"

 

If you copy this formula down, it will work off the dates in column B.

 

Cheers

JB AKA Mr Macro

 

PS Yes, the more difficult bit is conditional formatting. As you work for the public sector, does it mean that those people won't get their pay review if I don't tell you how to do it? If so, I could save the taxpayer a few quid.

Edited 25 January, 2010 by Johnny Bognor

[Clapham Saint]

Which version of Excel are you using?

 

If you put today's date in a free cell "=TODAY()" you can run the conditional formatting from the difference between the cells you want the rule to apply to and the cell with today's date.

[hamster]

If the name is "Hamster" in column A and "25/01/2010" is in column B, then you need to add 365 days to column B.

 

Therefore in column C, you should have "=B1+365"

 

If you copy this formula down, it will work off the dates in column B.

 

Cheers

JB AKA Mr Macro

 

PS Yes, the more difficult bit is conditional formatting. As you work for the public sector, does it mean that those people won't get their pay review if I don't tell you how to do it? If so, I could save the taxpayer a few quid.

 

First things first.

 

I did as you said and put 01/03/2010 in Column 'B' My b/day)

 

When I copied that formula into the adjacent (that means 'next to') the resultant date was '30/12/1900"

 

:confused:

 

I will attack your later comments once you have solved my problem if you don't mind.

[hamster]

Aha, Eureka, you were on the right track Johnny but I have just tinkered with your faulty formula and can reveal that it should have read =SUM(B1+365)"

 

You are obviously good though, please carry on MR Marco

[hamster]

Which version of Excel are you using?

 

If you put today's date in a free cell "=TODAY()" you can run the conditional formatting from the difference between the cells you want the rule to apply to and the cell with today's date.

 

At home? I am using an old 2003 version. At work I am pretty confident that it won;t be much newer than that as you lot don;t pay enough taxes. ;-)

 

Re the cf bit, I will tinker but please keep this coming as I am already up to half a zillion brownie points with JB's advice.

 

PS

JB, this will potentially mean that your Grandmother get's her annual review on time! Don;t do this for me, do it for your dear old Granny. I could suggest that we pay some chap with a degree a couple of thou and he will chain us to a contract that will see him safely into retirement, the choise dear taxpayer IS YOURS.

[Johnny Bognor]

Which version of Excel are you using?

 

If you put today's date in a free cell "=TODAY()" you can run the conditional formatting from the difference between the cells you want the rule to apply to and the cell with today's date.

 

If you have Excel 2007, then you don't need TODAY().

 

If we have:

A1 = Hamster

B1 = 25/02/09

C1 = "=B1+365"

 

Then highlight C1 and click on Home / Conditional Formatting / Highlight Cell Rules / A date occuring

 

Then select next month from the drop down menu. This will apply the formatting to cell C1 if the date occurs within the next month

 

If using 2003, then you will need to do a bit more work.

 

BTW Hamster, my formula is technically correct, see below:

 

Edited 25 January, 2010 by Johnny Bognor

[hamster]

btw Clapham, sorry but you are going to have to talk to me as if I am an idiot on this one. I won;t be offended if you spell it out, promise mate.

[hamster]

btw Johnny, I am tachnically thick. You may regret taking this on, if so do not hesitate to say so please. It's not fair of me to take up your time. Seriously, even I am off to the pub at nine and only I sit on my arse all day.

 

If at any point you think that I just won;t get this I could send you what i have so far (the 'Hamster and mrs hamster' version of course and you could send it back finished?

[Johnny Bognor]

If using Excel 2007, then highlight C1 and click on Conditional Formatting / Highlight Cell Rules / A date occuring (on the Home tab)

 

 

 

 

 

Then select "next month" from the drop down menu. This will apply the formatting to cell C1 if the date occurs within the next month as per my previous post. If pink is not your colour, you can change it.

 

If you have 2003, give me a little while as dinner is waiting, I'm starving and the Mrs is getting ****ed off that I'm offering technical support to some stranger on the internet

Edited 25 January, 2010 by Johnny Bognor

[1576]

If using Excel 2007, then highlight C1 and click on Conditional Formatting / Highlight Cell Rules / A date occuring (on the Home tab)

 

 

 

 

 

Then select "next month" from the drop down menu. This will apply the formatting to cell C1 if the date occurs within the next month as per my previous post. If pink is not your colour, you can change it.

 

If you have 2003, give me a little while as dinner is waiting, I'm starving and the Mrs is getting ****ed off that I'm offering technical support to some stranger on the internet

 

Should have had another burger when you were at Mcdonalds!

[hamster]

Before I waste any more of you's lot's time please let me check what Hants CC are using. I can tell that you are getting all turgid and am actually quite touched by your attentions but seriously I am going down the road for a few beers.

 

I'll let you all know the version sometime tomorrow and you can drool over it tomorrow while I am listening to the footie.

 

PS

I do love this plaice sometimes.

 

xx

hamster

and all of your little old Grannies.

[1576]

Before I waste any more of you's lot's time please let me check what Hants CC are using. I can tell that you are getting all turgid and am actually quite touched by your attentions but seriously I am going down the road for a few beers.

 

I'll let you all know the version sometime tomorrow and you can drool over it tomorrow while I am listening to the footie.

 

PS

I do love this plaice sometimes.

 

xx

hamster

and all of your little old Grannies.

 

Freudian slip?

 

I know we just got the skates in the cup so that must have been subliminal.

[Johnny Bognor]

Should have had another burger when you were at Mcdonalds!

 

Hey, I'm on a diet. You won't recognise me on saturday.

 

 

Johnny Bognor

Serving the Public Sector since 2010

[Yeovil Saint]

While B1+365 is okay for casual use, it will mess up at leap years so I like =date(year(B1)+1,month(B1),day(B1)) instead.

[Johnny Bognor]

While B1+365 is okay for casual use, it will mess up at leap years so I like =date(year(B1)+1,month(B1),day(B1)) instead.

 

Can't argue with that as that is the absolute solution...I was only trying to keep it simple.

[dubai_phil]

Excel is the route of all evil

 

All Excel should be destroyed.

 

Since Excel was invented Accountants and p1ss poor managers have hidden behind it to take over the world

 

All excel should be destroyed

 

All excel users should be forced to watch the skates every week standing naked with Red & White body painted shirts on while buckets of ice cold water are poured over them every 10 minutes.

 

Rant over

[Johnny Bognor]

Excel is the route of all evil

 

All Excel should be destroyed.

 

Since Excel was invented Accountants and p1ss poor managers have hidden behind it to take over the world

 

All excel should be destroyed

 

All excel users should be forced to watch the skates every week standing naked with Red & White body painted shirts on while buckets of ice cold water are poured over them every 10 minutes.

 

Rant over

 

Can't say I'm with you on that one. Spreadsheet software was the killer application that made the PC viable, without it, we would still all be using word processors and typewriters. Aside from email (and only until recently), spreadsheet software is/was the most important of all business software. It represents the hammer, saw and screwdriver of the modern business professional - as in any profession, a bad work man always blames his tools.

Edited 26 January, 2010 by Johnny Bognor

[hamster]

Freudian slip?

 

I know we just got the skates in the cup so that must have been subliminal.

 

Not quite. Well, very quite actually, I was heading out to the pub and had - wait for it - fish and chips!!! How spooky is that?

 

Actually I am lieing (sp), I put it in for all the teachers out there to test their bp's. I didn;t even eat whilst down the pub, I had lasagne beofre I went out. I feel bad now for lying (sp), and that guilt perhaps explains my stonking headache this morning. definitely NOT the London Pride.

[hamster]

While B1+365 is okay for casual use, it will mess up at leap years so I like =date(year(B1)+1,month(B1),day(B1)) instead.

 

Can't argue with that as that is the absolute solution...I was only trying to keep it simple.

 

fwiw we don't neeed to worry about leap years, but well done for putting yet another so called 'expert' in his place (sp). We just need something to replace the current (not so efficient) system of 'remembering' and 'writing stuff down'.

 

And whay might that be Johnny?

 

Thanks again people, I will attempt these solutions later today, when my head clears.

 

hamster

drain on the public purse

[hamster]

Excel is the route of all evil

 

All Excel should be destroyed.

 

Since Excel was invented Accountants and p1ss poor managers have hidden behind it to take over the world

 

All excel should be destroyed

 

All excel users should be forced to watch the skates every week standing naked with Red & White body painted shirts on while buckets of ice cold water are poured over them every 10 minutes.

 

Rant over

 

Can't say I'm with you on that one. Spreadsheet software was the killer application that made the PC viable, without it, we would still all be using word processors and typewriters. Aside from email (and only until recently), spreadsheet software is/was the most important of all business software. It represents the hammer, saw and screwdriver of the modern business professional - as in any profession, a bad work man always blames his tools.

 

 

Ther's only one way to settle this I am afriad........

 

 

and I am duty bound to sit in the gaffer's corner, sorry JB. Can this spat wait until i have saved you ungrateful taxpayers (obviously not you Phil, you being a 'I'm alright Jack' sort) a few bucks.

 

right, shiot, shower, trim the bush time, then off to work.

[hamster]

Just to update as to where I am with this, I have the 'plus 365 days' bit.

 

I can do a 'conditional format' on a cell to give it a red border (that will be fine for my purpose) when the date is later than today's date (which i have entered into a spare cell for practice purposes). So I am starting to understand how this works and some of the lingo ( true.false etc).

 

Now I need to make the cell refer to the target date ie. 1 month prior to or less. Does that make sense?

 

Somehow i need to make all these formulas refer to TODAY's DATE, bearing in mind that TODAY'S DATE must change in real time. This would be simple in Word as you can check the box saying "update automatically", but I can't find that in Excel.

 

Don't worry about the footie, I will tell you when we next score.

 

Really hope this makes sense? Questions welcome, but from the OP you should get the gist.

[Doctoroncall]

Instead of working with a cell with today's date, have a cell with a "trigger" date, i.e., today() minus 30. Use this cell for the conditional formating (when cell A is less than cell B) to mark when you're within the 30 day period of the target date.

 

HTH

Edited 27 January, 2010 by Doctoroncall

[hamster]

/\

Doc,

I am going to have to dissect your post as it is a bit too lingo'istic for me. I think that we are almost there.

 

I'll be back on tomorrow to let you all know how I am progressing.

 

I'm actually quite chuffed with myself as I am learning stuff without having to face the torment and torture that is 'Adult Education'. See, learning can be fun.

[hamster]

Major BUMP.

 

It's been a while and tbh I had forgotten all about this little project that I was 'volunteered' for. Alas a manager at work reminded me about it on Monday and I ahve agreed to 'teach' conditional formatting to a couple of our admin ladies next Wednesday. Pride and stupidity prevented me from admitting that i had forgotten how to do it and worse still lost the file that I had practised on! Sorry all as I know you gave a lot of time to my plight.

 

On a plus, when we opened the file, the girls had been using various formats for the DOB's. That bought me a bit of time as I needed to "go away and have a think". Gave them both the shakey-head, treatment and told them that they had wasted quite a few hours of my valuable time.

 

can i ask again for someone to explain how I do this please?

 

 

Requirements altered slightly too:

I shall use the column headings for ease, and I will highlight them in GREEN if I have completed the task and RED if I am still clueless.

 

TODAYS DATE - this is a single cell 'A2'.

 

CLIENT REF No. - done.

 

KEY WORKER - done

 

LAST NAME - done.

 

FIRST NAME - done.

 

ADDRESS - done.

 

DOB - is in 'dd-mm-yyyy' format.

 

AGE - needs to automatically show the service users age 'today'.

 

LAST REVIEW - shows the date of the service user's last annual review (updated manually by admin team in dd-mm-yyyy format).

 

NEXT REVIEW - needs to be 1 year from 'LAST REVIEW' date.

also

NEXT REVIEW - the cell border, shading or teaxt needs to be amber if the date is less than 28 days away.

also.

NEXT REVIEW - the cell border or shading needs to be red if the date has passed.

 

NOTE.

I have explained (tried to) that if this was done in Outlook, then I could help them to set reminders and send e-mails to relevant parties etc as and when but they want to persevere with the spreadsheet method. This would need a little time to set up but imho would suit our purposes perfectly, although admittedly we wouldn't be able to print off summaries for our beloved auditors.

 

Thanks in advance.

 

PS

i shall confirm which version of MS we have sometime tomorrow.

Edited 18 June, 2010 by hamster

[Whitey Grandad]

Does it work for Boxing Day?

[saint si]

To enter today's date in a cell, just use =today()

[Zurichsaint]

ok, here goes:

 

As Saint Si said, to enter today's date in cell A2, just type

=TODAY

in that cell.

 

For age, if DOB is in cell D3, the formula would be

=ROUNDDOWN((A2-D3)/365;0)

It won't be perfect (because of leap years), but its the simplest.

 

For next review date, suggest you use the one mentioned in one of the older posts:

Assuming last review is in cell E4:

=DATE(YEAR(E4)+1;MONTH(E4);DAY(E4))

 

For the formatting, like it says in the previous posts, it depends on the version of excel. You can do it through Conditional Formatting / Highlight Cell Rules / A date occuring in Excel 2007, or in the older version Format / conditional formatting.

 

Check what excel you've got first then we can answer that one.

 

Good luck!

[hamster]

Our Version is:

 

Windows Office Profession Edition 2003.

 

Yep it's probably way overdue an upgrade but then again, it works and it saves the taxpayer millions in new licences so can't complain really.

 

I shal be for thenext half hour trying to get a couple of them formulae in to my test document (found it thankfully) and update my progress. Cheers again in advance.

 

hamster

[hamster]

Sorted 'TODAY's date now, in our version I had to type "=TODAY ()"

[Zurichsaint]

Sorted 'TODAY's date now, in our version I had to type "=TODAY ()"

 

Quite right too - that's what my post should have said

[hamster]

Quite right too - that's what my post should have said

 

Tried a couple of the other formulas but I'm guessing that 2003 is a bit more convoluted, especially as the test file I created that worked with a few of them was done at home on 2007 Pro.

 

I shall persevere though.