help with date-based calculations
Posted by keke on August 21, 2001 12:22 PM
I have a three sheet workbook in XL2000. The first sheet (called Workflow data) contains the raw data for jobs moving thru a production process. The second sheet is a summary sheet that allows me to generate reports based on the raw data for my bosses. The third is a sheet I want to use for analysis of the production process.
The raw data includes dates (date quoted, date job comes in, date due, date finished). I want to have XL calculate things like: how many days between quote date and date job comes in, how many days from when job comes in to when it's completed, and a comparison of the finished date vs the due date. Not all jobs have a quote date but every job has all other dates.
What I'm having trouble with is:
1) if there is no quote date, I get a response like 37116 to the formula =('Workflow data'!F2-'Workflow data'!D2) where d2 is the date quoted (blank) and f2 is the date the job comes in. I want it to return a blank.
2) if the number of days between the finished date and the due date is a negative number (ie the job was completed before the due date) the formula =('Workflow data'!H5-'Workflow data'!P5) where p5 is date finished and h5 is due date does not return a negative number. I thought I could do conditional formatting on the formula to get the negative numbers to show up in red but it will not allow me to do conditional formatting on data from other worksheets.
Can you guys help? You were great with my pivot table question -- altho I have not been able to get back to it as it is in the "lost postings" from late July.
Thanks!
keke
Posted by Aladin Akyurek on August 21, 2001 12:33 PM
Use:
=IF(ISNUMBER('Workflow data'!F2),'Workflow data'!F2-'Workflow data'!D2,"")
Use:
=IF('Workflow data'!H5>='Workflow data'!P5,'Workflow data'!H5-'Workflow data'!P5,-('Workflow data'!H5-'Workflow data'!P5))
Aladin
Posted by keke on August 21, 2001 2:04 PM
Aladin, thanks for your response. Unfortunately it didn't work... :-(
None of the cells that had contained a number changed when I inserted the new formulae. In the first case I did notice that if I inserted the new formula into cells corresponding to uncompleted ranges in 'Workflow data' (ie. quote date and date job in are both blank) it returns a blank. Do I need to do anything else, like force a refresh of some kind?
TIA,
keke
Posted by Aladin Akyurek on August 21, 2001 2:28 PM
The first formula should be:
=IF(ISNUMBER('Workflow data'!D2),'Workflow data'!F2-'Workflow data'!D2,"")
or, if both D2 and F2 can be blank, better use:
=IF(AND(ISNUMBER('Workflow data'!D2),ISNUMBER('Workflow data'!F2)),'Workflow data'!F2-'Workflow data'!D2,"")
You can also expand the 2nd formula as:
=IF(AND(ISNUMBER('Workflow data'!H5),ISNUMBER('Workflow data'!P5)),IF('Workflow data'!H5>='Workflow data'!P5,'Workflow data'!H5-'Workflow data'!P5,-('Workflow data'!H5-'Workflow data'!P5)),"")
The cells where these formula are entered must have a number or general format.
Aladin
The cells of interest contain dates, right. They must be also date-formatted, not text-formatted.
In the first case I did notice that if I inserted the new formula into cells corresponding to uncompleted ranges in 'Workflow data' (ie. quote date and date job in are both blank) it returns a blank. Do I need to do anything else, like force a refresh of some kind?
Posted by keke on August 21, 2001 3:20 PM
This one worked great! Thanks!
I am still getting positive numbers as a result. For instance, 'Workflow data'!h5 is 8/17/01, while 'Workflow data'!p5 is 8/16/01. The result in the analysis cell should be -1 (the analysis cell format is "number", with negative numbers to appear as -1234) but it is 1. The actual digits are correct.
Hmmm, I didn't think this would be as complicated as it is. You are being a great help!
keke
Posted by Aladin Akyurek on August 21, 2001 3:41 PM
Keke,
It seems I keep forgetting the meanings of the cells!
The 2nd is:
Aladin
Posted by keke on August 21, 2001 4:15 PM
Aladin, thanks so much! 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:
=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)
(this example is from row 6) and it worked wonderfully.
I appreciate your help. Now I shall go back and try and figure out what it was you did so I can apply it again if I need it!
keke
Posted by keke on August 28, 2001 9:37 AM
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
