Date Difference in excel

[karthisiva]

Hi i have a problem in finding the difference in excel date.

criteria is as below

c2 - order date - for eg 23/05/2015
d2 - vendor promise a date that the prod will dispat for eg 05/06/2015

E2 - i want a number which should be (today - (d2-c2)) and if the today date crossed vendor promissed date the color should change to Red if the today date is less than vendor promised date it should be in green.

can you please help me out to solve this

Thanks

 

[Albatros]

You need conditional formatting for that.

 

[karthisiva]

i need the formula to do the above calculation, conditional format wont find the date diff i believe

 

[gaz_chops]

Not sure i understand, do you not simply need to use below in conditional formatting?

For Red =d2>today()
For Green =d2<=today()

 

[Albatros]

Of course.
You need a formula and conditional formatting.

You set a new rule for conditional formatting. home>>>Conditional Formatting>>>>New rule

Then select "use formula to determine which cells to format"
Then select the colouring under button format ...Fill

For red enter this formula:

=now()>$D1

On column D you have the delivery dates promised by client

Now you set a new rule for green:


=now()<=$D1

Whichever cells you apply these two rules to, will show either red or green depending on whether today's date is bigger or not than promised delivered date.

For "applies to" you could use the entire column E

 

[gaz_chops]

Of course.
You need a formula and conditional formatting.

You set a new rule for conditional formatting. home>>>Conditional Formatting>>>>New rule

Then select "use formula to determine which cells to format"
Then select the colouring under button format ...Fill

For red enter this formula:

=now()>$D1

On column D you have the delivery dates promised by client

Now you set a new rule for green:


=now()<=$D1

Whichever cells you apply these two rules to, will show either red or green depending on whether today's date is bigger or not than promised delivered date.

For "applies to" you could use the entire column E

=now()
will give the time as well, which is not really necessary
=today() will suffice, as per post #4

 

[karthisiva]

Worked Well Thanks All

 

[gaz_chops]

You're welcome.