Difference between 2 dates applications

[timh1234]

Hello,

This is quite an awkward problem to describe, but bear with me...

I have a spreadsheet, including 2 columns. Let's say column A has the header Priority, and Column B has the header Due Date.

The user will assign an initial numeric value to Priority and assign a Due Date, for a given project. A higher number in the Priority column indicates a higher priority.

This is where the complication comes. Is there a way to increase the value in the Priority box by, say 50, for every week closer to the Due Date we get, based on the date today.

I don't know whether this needs to be VBA or if there is just a simple formula you can enter into a cell. Either way, I'm stumped.

Thanks in advance.

[timh1234]

To perhaps make this a little clearer, the user enters the following:

Priority
100

Due Date
01/03/2012

The date today is 09/02/2012.

In one week, ie 16/02/2012, the Priority value increases to 150.
In 2 weeks, ie 23/02/2012, the Priority value increases to 200. etc

Also, is there a way to stop the Priority value increasing, once the Due Date has been reached.

Thanks.

[westconn1]

In one week, ie 16/02/2012, the Priority value increases to 150.
In 2 weeks, ie 23/02/2012, the Priority value increases to 200. etc

this contradicts you first post

i would have a maximum priority then decrease for each extra week
example max of 500 less 50 for each week zeroes out in 10 weeks
try something like

weeks = DateDiff("w", Date, Cells(rw, 2)) + 1
Cells(rw, 1) = 500 * (10 - weeks) * 0.1
If DateDiff("d", Date, Cells(rw, 2)) = 0 Then Cells(rw, 1) = "too late"

[timh1234]

I see your point, but unfortunately, this is part of a project for someone else. They have been using this priority system for a while, and it is up to me to find a more automated version. I think they would be quite apprehensive if I were to change the way they prioritise everything. Is there no way to implement the slightly more awkward process that I first suggested?

[opus]

You would need more data to do such a calculation.
The Input-Date and the Initial Priority are needed, having them you can calculate the Actual Priority.

Weeks = DateDiff("w", Date, InputDate))  
Actual Priority=Weeks*100 + Initial Priority

[westconn1]

more awkward process that I first suggested?

you did not give enough information to provide a calculation of priorities based on your example
need starting values etc.
still you should be able to manage a calculation or a select case of the number of weeks

select case weeks
  case 1: cells(rw, 1) = 450
  case 2: cells(rw, 1) = 400
  case 3: cells(rw, 1) = 350
  ' etc
end select