Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum if statement with a left statement Eric D Excel Discussion (Misc queries) 4 July 23rd 08 05:31 PM
SUMIF statement with AND statement Eric D Excel Discussion (Misc queries) 2 July 14th 08 07:24 PM
appending and IF statement to an existing IF statement spence Excel Worksheet Functions 1 March 1st 06 12:00 AM
If statement and Isblank statement Rodney C. Excel Worksheet Functions 0 January 18th 05 09:39 PM
Help please, IF statement/SUMIF statement Brad_A Excel Worksheet Functions 23 January 11th 05 03:24 PM


All times are GMT +1. The time now is 12:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"