m_woychick
New Member
- Joined
- Mar 22, 2004
- Messages
- 33
I am trying to calculate the number of days it takes for an item to get from point a to point b. I get two reports--one that shows the object name and "point a" date and a second report that shows object name and "point b" date. All the object names in the first report may or may not be in the second report.
I put the report data into 4 columns:
object name a, point a, object name b, point b
My formula looks to see if object name a is the object name b column, subtract object name a's point a time from object name b's point b time.
using sumproduct, the formula looks like this
=SUMPRODUCT((A33=$C$32:$C$1032)*-(B33-$D$32:$D$1032))
I am getting some odd results, however. Here are some actual examples. The first looks OK, but the second should be ~17 days.
11/11/2003 0:07-11/3/2003 6:46=7.722824074
11/24/2003 8:17-11/7/2003 2:52=59.18696759
My guess is that I'm using sumproduct incorrectly or that I should be using some other function altogether. What I want to be able to do is see if the same object name exists in another column, and if so, subtract one number from another in different columns.
Any advice/insight would be great.
I put the report data into 4 columns:
object name a, point a, object name b, point b
My formula looks to see if object name a is the object name b column, subtract object name a's point a time from object name b's point b time.
using sumproduct, the formula looks like this
=SUMPRODUCT((A33=$C$32:$C$1032)*-(B33-$D$32:$D$1032))
I am getting some odd results, however. Here are some actual examples. The first looks OK, but the second should be ~17 days.
11/11/2003 0:07-11/3/2003 6:46=7.722824074
11/24/2003 8:17-11/7/2003 2:52=59.18696759
My guess is that I'm using sumproduct incorrectly or that I should be using some other function altogether. What I want to be able to do is see if the same object name exists in another column, and if so, subtract one number from another in different columns.
Any advice/insight would be great.