Timestamp Conditional Formating

[TonyforVBA]

I'm looking to add some conditional formating to the attahced file. Idealy I would like the formula to take a tamestamp at the time of opening the file and update update all records within the file based on the below statment.

I was wondering if it is possible to this by a nested If statement within Excel function or would I be better off aproching this in VB?

I've attached a sample book and the basic logic for the statement I need is.


Status Case Statement
If B Is not Null and E is Null Then 'Pending'

If E is not null
And 'Now' is Greater then E
And F is null Then 'OverDue'

If F is Less than or Equal to E Then 'Complete'



Fomating Case Statement
If Status is Pending then 'Orange Font'
If Status is Overdue and Complete Date is Null then 'Red Font'

[Fotis1991]

Hi

I believe that we can do this, using gormulas and CF, but....mybe you need to re-look the conditions. What i mean is thet in H7, you wait to sat "Pending" And the Condition for Pending, must be

Please Login or Register  to view this content.

F6 IS NOT Null(Empty)

And condition for "Complete", is this.

Please Login or Register  to view this content.

Which is the Corect, because F6 & E6 are same days..

[TonyforVBA]

Thanks for that Fotis,

I guess my main query is around 'And 'Now' is Greater then E '
Is it possible for Excel to pick up the current date & time from 'Now' outside of VB?

[Fotis1991]

Yes it is Tony.

Look here how to do this.

http://www.mcgimpsey.com/excel/timestamp.html

So do you still need what you asked for(Formulas &CF) or not any more?

[TonyforVBA]

I've almost got the formula sussed for what I need it to do. I've re-attached the work sheet and you will see that the correct Status is being applied in each case. However, I want it so that if there is no record there at all, the formula will return a null.

I attempted this alteration to the formula within the spreadsheet, but no joy. I'm not overly familiar with the syntax for Excel Formula.

Please Login or Register  to view this content.

[Fotis1991]

One more condition, Tony.

Please Login or Register  to view this content.

I also did the CF.

Is this, works for you?

[Fotis1991]

Apologize.. You use Excel 2003!

[TonyforVBA]

That's spot on Fotis. Thanks for your help with this, your a star!
Enjoy the weekend!

[Fotis1991]

You are welcome

Thanks for the feed back and also for reb*.

Have a nice weekend too!