Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Than Statement
I was not sure on a descriptive Subject for this so I used If Than statement
due to if I were using Crystal Reports that is how I would do this: I pulled this data from a Crystal report, the below are order promise dates and order receipt dates. The first date is the Promise date the second is the receipt date so in order to track on time shipments I would take the first column and subtract the second however I came across several issues. 1. is when you see a blank line that means the first row after that is a different part number then the number above. 2. is that we may receive several shipments of the same part with the same promise date. 3. is that you will see on the last line that there are three rows and the last has a different promise date which means on that order for that part number two different promise dates were issued. I started out with an IF statement that stated that if receipt date is receipt date above it then subtract that receipt date from the promise date to get days late. This worked for two row but caused issues when it got to a blank row and a single row of dates. So I am not sure how to get past that, then on the last group when it changes promise dates I don't know how to get past that issue either. Any ideas for me to try?? 3/28/2008 5/9/2008 4/11/2008 5/9/2008 4/11/2008 5/16/2008 5/16/2008 5/2/2008 5/16/2008 5/9/2008 6/13/2008 5/9/2008 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Than Statement
Hi,
Post a better sample of your data, name the columns add other columns of info so we can determine what else might be used to solve the problem. -- Thanks, Shane Devenshire "Xfree" wrote: I was not sure on a descriptive Subject for this so I used If Than statement due to if I were using Crystal Reports that is how I would do this: I pulled this data from a Crystal report, the below are order promise dates and order receipt dates. The first date is the Promise date the second is the receipt date so in order to track on time shipments I would take the first column and subtract the second however I came across several issues. 1. is when you see a blank line that means the first row after that is a different part number then the number above. 2. is that we may receive several shipments of the same part with the same promise date. 3. is that you will see on the last line that there are three rows and the last has a different promise date which means on that order for that part number two different promise dates were issued. I started out with an IF statement that stated that if receipt date is receipt date above it then subtract that receipt date from the promise date to get days late. This worked for two row but caused issues when it got to a blank row and a single row of dates. So I am not sure how to get past that, then on the last group when it changes promise dates I don't know how to get past that issue either. Any ideas for me to try?? 3/28/2008 5/9/2008 4/11/2008 5/9/2008 4/11/2008 5/16/2008 5/16/2008 5/2/2008 5/16/2008 5/9/2008 6/13/2008 5/9/2008 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Than Statement
Shane,
Below is what is on the spread sheet, you can see that when the supplier ships partial shipments I need to use the latest ReceiptDate to find how late they were from Promise Date to Complete Qt Ordered. Then on the last part, you will see how the same part can have multiple promise dates for different Qt's Part # PoNo QtOrdered QtReceived PromiseDate ReceiptDate Days Early/Late 16-109 21005 300 300 03/28/08 05/09/08 ( Promise-Receipt) Blank Line______________________________________________ ____________ 16-106 21311 250 185 04/11/08 05/09/08 21311 250 65 04/11/08 05/16/08 (Promise-latestRecipt) Blank Line______________________________________________ ____________ 14-107 21493 360 217 05/16/08 05/02/08 21493 360 143 05/16/08 05/09/08 (Promise-latestRecipt) 21493 360 360 06/16/08 06/18/08 ( Promise-Receipt) (on the last line for part 14-107 / PO 21493 you see it has a diffrent Promise date) Blank Line________________ "ShaneDevenshire" wrote: Hi, Post a better sample of your data, name the columns add other columns of info so we can determine what else might be used to solve the problem. -- Thanks, Shane Devenshire "Xfree" wrote: I was not sure on a descriptive Subject for this so I used If Than statement due to if I were using Crystal Reports that is how I would do this: I pulled this data from a Crystal report, the below are order promise dates and order receipt dates. The first date is the Promise date the second is the receipt date so in order to track on time shipments I would take the first column and subtract the second however I came across several issues. 1. is when you see a blank line that means the first row after that is a different part number then the number above. 2. is that we may receive several shipments of the same part with the same promise date. 3. is that you will see on the last line that there are three rows and the last has a different promise date which means on that order for that part number two different promise dates were issued. I started out with an IF statement that stated that if receipt date is receipt date above it then subtract that receipt date from the promise date to get days late. This worked for two row but caused issues when it got to a blank row and a single row of dates. So I am not sure how to get past that, then on the last group when it changes promise dates I don't know how to get past that issue either. Any ideas for me to try?? 3/28/2008 5/9/2008 4/11/2008 5/9/2008 4/11/2008 5/16/2008 5/16/2008 5/2/2008 5/16/2008 5/9/2008 6/13/2008 5/9/2008 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Than Statement
Sorry Shane I tried to format that so it was all columns were in line but it
changed it when I posted. "Xfree" wrote: Shane, Below is what is on the spread sheet, you can see that when the supplier ships partial shipments I need to use the latest ReceiptDate to find how late they were from Promise Date to Complete Qt Ordered. Then on the last part, you will see how the same part can have multiple promise dates for different Qt's Part # PoNo QtOrdered QtReceived PromiseDate ReceiptDate Days Early/Late 16-109 21005 300 300 03/28/08 05/09/08 ( Promise-Receipt) Blank Line______________________________________________ ____________ 16-106 21311 250 185 04/11/08 05/09/08 21311 250 65 04/11/08 05/16/08 (Promise-latestRecipt) Blank Line______________________________________________ ____________ 14-107 21493 360 217 05/16/08 05/02/08 21493 360 143 05/16/08 05/09/08 (Promise-latestRecipt) 21493 360 360 06/16/08 06/18/08 ( Promise-Receipt) (on the last line for part 14-107 / PO 21493 you see it has a diffrent Promise date) Blank Line________________ "ShaneDevenshire" wrote: Hi, Post a better sample of your data, name the columns add other columns of info so we can determine what else might be used to solve the problem. -- Thanks, Shane Devenshire "Xfree" wrote: I was not sure on a descriptive Subject for this so I used If Than statement due to if I were using Crystal Reports that is how I would do this: I pulled this data from a Crystal report, the below are order promise dates and order receipt dates. The first date is the Promise date the second is the receipt date so in order to track on time shipments I would take the first column and subtract the second however I came across several issues. 1. is when you see a blank line that means the first row after that is a different part number then the number above. 2. is that we may receive several shipments of the same part with the same promise date. 3. is that you will see on the last line that there are three rows and the last has a different promise date which means on that order for that part number two different promise dates were issued. I started out with an IF statement that stated that if receipt date is receipt date above it then subtract that receipt date from the promise date to get days late. This worked for two row but caused issues when it got to a blank row and a single row of dates. So I am not sure how to get past that, then on the last group when it changes promise dates I don't know how to get past that issue either. Any ideas for me to try?? 3/28/2008 5/9/2008 4/11/2008 5/9/2008 4/11/2008 5/16/2008 5/16/2008 5/2/2008 5/16/2008 5/9/2008 6/13/2008 5/9/2008 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum if statement with a left statement | Excel Discussion (Misc queries) | |||
SUMIF statement with AND statement | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
If statement and Isblank statement | Excel Worksheet Functions | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions |