Excel 2007 - Assistance with Conditional Formatting 1

[ladyck3]

I will try to be as brief as possible. I have a master spreadsheet that is linked to individual workbooks one per product. Each product workbook has 4 tabs (one for each department and with their tasks listed which, when complete total 100%)

The Master turns 100% when all 4 tabs total 100% each but the problem is... on the master is a DUE DATE. Eventually all of the product's worksheets will total 100% completion but the problem is, they want to have a column on the Master that denotes whether the 100% was attained on time or was completed after the fact.

I am not a VBA expert.. I an open the editor and save something that someone else writes but if it breaks, I have no clue how to fix it.

With that said, is there a way to accomplish this conditional formatting or having a new column actually somehow show the date when the product workbook is completed without manually having to keep track of 4 different departments and their completion times?

I hope I made sense to everyone and thank you in advance....

LadyCK3
aka: Laurie

 

[ladyck3]

HELP...
I finally got to this project and I am sure (well 99% sure) that I understand the formula but my results are screwed up. I thought maybe it was the date issue in Excel where you had to check something about 1904 in the options but I can't find that.

Here's what I have.... Using Kurupt55's formula the results come back as 01/00/1900.

My Percentage used as the base is in Cell D4
I put the first formula into cell F3 and changed it as follows: =IF(D4=1,TODAY(),0)
The second part of the formula in in E4 (Next to the %) and it reads: =IF(E4=0,F3,E4)

If I understand the original correctly I've made the following corrolations:
MY CONFIG: A1 - D4 Percentage
B1 - F3 =IF(D4=1,TODAY(),0)
C1 - E4 =IF(E4=0,F3,E4)

Is this a formula or formatting issue? If its formatting what in the WORLD am I doing wrong? its custom formatted to mm/dd/yyyy.

Thanks!




LadyCK3
aka: Laurie

 

[SkipVought]

Using Kurupt55's formula the results come back as 01/00/1900.

Well that's perfectly understandable as your formula is...
[tt]
=IF(D4=1,TODAY(),0)
[/tt]
and 01/00/1900 IS ZERO and if you don't know why, faq68-5827.

It has nothing to do with 1904. It has everything to do with 1/1/1900.

What is it that you DO want to happen under each instance?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

To reiterate what I and others have said a bit more plainly:

The route you're taking is a terrible idea.

Kurupt55's formula is very clever and he should be commended for it. But even he advised you not to go this route given that other users will be interacting with this spreadsheet.

Whatever problems you might have now pale in comparison to what will happen when, eventually, someone does something they shouldn't and you lose all of the data. All of it. Lost.

The fact that someone has opened an email doesn't mean that they've read it. And reading an email doesn't mean that they've comprehended it. And comprehending it doesn't mean they'll remember it. Even if every person on the team reads, comprehends and remembers the contents of the email, it doesn't mean one of them won't do something they shouldn't.

I'm sorry, but I think what you're doing is worse than wasting your time. If you're not familiar with VBA, I know a great site with generous contributors who would help you come up with functional code.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

DITTO John!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ladyck3]

Thank you all for your time/patience.
Off to the VBA Forum.

LadyCK3
aka: Laurie