Difference between 2 dates applications
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.
Re: Difference between 2 dates applications
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.
Re: Difference between 2 dates applications
Quote:
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
vb Code:
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"
Re: Difference between 2 dates applications
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?
Re: Difference between 2 dates applications
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.
Code:
Weeks = DateDiff("w", Date, InputDate))
Actual Priority=Weeks*100 + Initial Priority
Re: Difference between 2 dates applications
Quote:
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
vb Code:
select case weeks
case 1: cells(rw, 1) = 450
case 2: cells(rw, 1) = 400
case 3: cells(rw, 1) = 350
' etc
end select