help with date-based calculations -- again
Posted by keke on September 04, 2001 2:53 PM
Back in mid-August, Aladin helped me come up with a formula to take a due date and a finished date and compare them, returning a negative number if the job was finished before the due date.
My message to Aladin said:
I found I had to make one minor change to the formula since it said I had too many arguments. So what I ended up with was:
data'!H6-'Workflow data'!P6)),'Workflow data'!H6-'Workflow data'!P6)
Well, it SEEMED to work. Turns out it is fine if the number to be returned is negative. If we miss our due date (due date - delivery date = 1 or more) it
returns a FALSE value rather than the number. Could it be that "too many argumants" thing I deleted?
keke
Posted by Aladin Akyurek on September 04, 2001 3:26 PM
Yes, I believe so. And I now see why you'd get "too many arguments": One paren was misplaced. The full formula should be:
=IF(AND(ISNUMBER('Workflow data'!H6),ISNUMBER('Workflow data'!P6)),IF('Workflow data'!H6>='Workflow data'!P6,-('Workflow data'!H6-'Workflow data'!P6),'Workflow data'!H6-'Workflow data'!P6),"")
Will you try this one?
Aladin
Posted by keke on September 04, 2001 3:44 PM
Aladin wrote:
OK, I did. H7 (due date) is 8/27/01 while P7 (finished date) is 8/28/01. It now returns -1 instead of FALSE but it should return 1. ?!?
keke
Posted by Aladin Akyurek on September 04, 2001 4:08 PM
keke,
I switched the last arg of the 2nd IF. I hope this ends our misery.
=IF(AND(ISNUMBER('Workflow data'!H6),ISNUMBER('Workflow data'!P6)),IF('Workflow data'!H6>='Workflow data'!P6,-('Workflow data'!H6-'Workflow data'!P6),'Workflow data'!P6-'Workflow data'!H6),"")
This formula will return neg nums if due date > finished date. If finished date > due date, it will return pos nums.
If I got it wrong again, please be patient. We'll clear it up the way you want it.
Regards,
Aladin
Posted by keke on September 05, 2001 9:12 AM
date calc's work perfectly now -- big thanks
Nope. This worked terrific. I formatted the negative numbers in red, and put a COUNTIF into the spreadsheet to track how many on time and advance deliveries we do as well.
Thanks again, Aladin.
keke
