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.