# Date Difference in excel

#### karthisiva

##### New Member
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.

Thanks

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Albatros

##### New Member
You need conditional formatting for that.

#### karthisiva

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

#### gaz_chops

##### Well-known Member
Not sure i understand, do you not simply need to use below in conditional formatting?

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

#### Albatros

##### New Member
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

Last edited:

#### gaz_chops

##### Well-known Member
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

##### New Member
Worked Well Thanks All

You're welcome.

Replies
4
Views
1K
Replies
3
Views
801
Replies
8
Views
2K
Replies
3
Views
2K
Replies
5
Views
6K

1,195,831
Messages
6,011,843
Members
441,650
Latest member
ceyoung75

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back