MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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