How to subtract two dates and put result in a different cell 1

[vba317]

I have been struggling with how to subtract two cells and have the result show in a third cell. So for example I have cell B5 with a fixed date. I have cells B9 through B91 that have different values in them. I would like to subtract B9-B5 and have the result go in E9. B10-B5 and the result to go in cell E10. Is this possible?

 

[PHV]

In E9 put this formula:
=B9-B$5
Then copy down through E91

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

BTW,

This is not a VBA question.

Please post further spreadsheet questions in forum68.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

I was hoping to use the range object. I am sorry for not being clearer in my question. PHV's solution would work But I wanted to use the range method because I am using a macro that I have to run sometimes 4 or five times a day that resorts the clients order every day. I need to enter a value in the E column not a formula.
Tom

 

[PHV]

A starting point:

For i = 9 To 91
  Cells(i, 5) = Cells(i, 2) - Cells(5, 2)
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

PHV,

I had to add something to the formula to refine the answer.

 For i = 9 To 91
        Cells(i, 5) = Cells(i, 2) - (Cells(5, 2) - 1)
    Next

This formula will give me what day in the cycle I am in, instead of 0. The problem I am having is how to add workdays to your formula.

So what happens is Cell 9,2 = 10/31/2013 and Cell 5,2 = 10/31/2013 so I get the answer I want 1.0
The problem happens when I get to Monday Nov 4. The calculation is 5 I need it to be 3, because that is the 3rd workday. Is this possible?

 

[PHV]

And what about holidays ?
Anyway, write your own function calculating the number of days you want (there are plenty of examples here at TT and on the web).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886