| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find within Date Range
Is there a way with a given date that it knows which date range or period
that it belongs to? If I have a date of 8/31/08, I want find which range it is in between. Therefore, it is after 08/11/08 and before 09/07/08. Once I find it, I want to compute the 8/31/08 minus 08/11/08 then multiple the volume times the given rate for that range. Does this make sense? I appreciate any help. Thanks. Period 1 12/31/07 01/27/08 Period 2 01/28/08 02/24/08 Period 3 02/25/08 03/23/08 Period 4 03/24/08 04/20/08 Period 5 04/21/08 05/18/08 Period 6 05/19/08 06/15/08 Period 7 06/16/08 07/13/08 Period 8 07/14/08 08/10/08 Period 9 08/11/08 09/07/08 Period 10 09/08/08 10/05/08 Period 11 10/06/08 11/02/08 Period 12 11/03/08 11/30/08 Period 13 12/01/08 12/28/08 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find within Date Range
The difference for 8/31/08 will be :
=IF(ISNA(MATCH(D1,B:B,0)),D1-VLOOKUP(D1,B:B,1,1),D1) HTH Daniel Is there a way with a given date that it knows which date range or period that it belongs to? If I have a date of 8/31/08, I want find which range it is in between. Therefore, it is after 08/11/08 and before 09/07/08. Once I find it, I want to compute the 8/31/08 minus 08/11/08 then multiple the volume times the given rate for that range. Does this make sense? I appreciate any help. Thanks. Period 1 12/31/07 01/27/08 Period 2 01/28/08 02/24/08 Period 3 02/25/08 03/23/08 Period 4 03/24/08 04/20/08 Period 5 04/21/08 05/18/08 Period 6 05/19/08 06/15/08 Period 7 06/16/08 07/13/08 Period 8 07/14/08 08/10/08 Period 9 08/11/08 09/07/08 Period 10 09/08/08 10/05/08 Period 11 10/06/08 11/02/08 Period 12 11/03/08 11/30/08 Period 13 12/01/08 12/28/08 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find within Date Range
Daniel,
Thanks for your help on this. Can you please interpret this in words what you are trying to do? I like to understand what this does for me. Rich "Daniel.C" wrote: The difference for 8/31/08 will be : =IF(ISNA(MATCH(D1,B:B,0)),D1-VLOOKUP(D1,B:B,1,1),D1) HTH Daniel Is there a way with a given date that it knows which date range or period that it belongs to? If I have a date of 8/31/08, I want find which range it is in between. Therefore, it is after 08/11/08 and before 09/07/08. Once I find it, I want to compute the 8/31/08 minus 08/11/08 then multiple the volume times the given rate for that range. Does this make sense? I appreciate any help. Thanks. Period 1 12/31/07 01/27/08 Period 2 01/28/08 02/24/08 Period 3 02/25/08 03/23/08 Period 4 03/24/08 04/20/08 Period 5 04/21/08 05/18/08 Period 6 05/19/08 06/15/08 Period 7 06/16/08 07/13/08 Period 8 07/14/08 08/10/08 Period 9 08/11/08 09/07/08 Period 10 09/08/08 10/05/08 Period 11 10/06/08 11/02/08 Period 12 11/03/08 11/30/08 Period 13 12/01/08 12/28/08 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find within Date Range
Sorry, should be :
=IF(ISNA(MATCH(D1,B:B,0)),D1-VLOOKUP(D1,B:B,1,1),0) I am assuming that product # ar in A column, first column of dates in B and second column of dates in C. 8/31/08 is D1. Column C is not needed, since there are no gaps between dates. The formula can be interpreted as follows : If there is no exact match betwwen D1 and column B, compute D1 - first date in B inferior to D1, else 0 (exact match). Daniel Daniel, Thanks for your help on this. Can you please interpret this in words what you are trying to do? I like to understand what this does for me. Rich "Daniel.C" wrote: The difference for 8/31/08 will be : =IF(ISNA(MATCH(D1,B:B,0)),D1-VLOOKUP(D1,B:B,1,1),D1) HTH Daniel Is there a way with a given date that it knows which date range or period that it belongs to? If I have a date of 8/31/08, I want find which range it is in between. Therefore, it is after 08/11/08 and before 09/07/08. Once I find it, I want to compute the 8/31/08 minus 08/11/08 then multiple the volume times the given rate for that range. Does this make sense? I appreciate any help. Thanks. Period 1 12/31/07 01/27/08 Period 2 01/28/08 02/24/08 Period 3 02/25/08 03/23/08 Period 4 03/24/08 04/20/08 Period 5 04/21/08 05/18/08 Period 6 05/19/08 06/15/08 Period 7 06/16/08 07/13/08 Period 8 07/14/08 08/10/08 Period 9 08/11/08 09/07/08 Period 10 09/08/08 10/05/08 Period 11 10/06/08 11/02/08 Period 12 11/03/08 11/30/08 Period 13 12/01/08 12/28/08 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find within Date Range
Thank You.
"Daniel.C" wrote: Sorry, should be : =IF(ISNA(MATCH(D1,B:B,0)),D1-VLOOKUP(D1,B:B,1,1),0) I am assuming that product # ar in A column, first column of dates in B and second column of dates in C. 8/31/08 is D1. Column C is not needed, since there are no gaps between dates. The formula can be interpreted as follows : If there is no exact match betwwen D1 and column B, compute D1 - first date in B inferior to D1, else 0 (exact match). Daniel Daniel, Thanks for your help on this. Can you please interpret this in words what you are trying to do? I like to understand what this does for me. Rich "Daniel.C" wrote: The difference for 8/31/08 will be : =IF(ISNA(MATCH(D1,B:B,0)),D1-VLOOKUP(D1,B:B,1,1),D1) HTH Daniel Is there a way with a given date that it knows which date range or period that it belongs to? If I have a date of 8/31/08, I want find which range it is in between. Therefore, it is after 08/11/08 and before 09/07/08. Once I find it, I want to compute the 8/31/08 minus 08/11/08 then multiple the volume times the given rate for that range. Does this make sense? I appreciate any help. Thanks. Period 1 12/31/07 01/27/08 Period 2 01/28/08 02/24/08 Period 3 02/25/08 03/23/08 Period 4 03/24/08 04/20/08 Period 5 04/21/08 05/18/08 Period 6 05/19/08 06/15/08 Period 7 06/16/08 07/13/08 Period 8 07/14/08 08/10/08 Period 9 08/11/08 09/07/08 Period 10 09/08/08 10/05/08 Period 11 10/06/08 11/02/08 Period 12 11/03/08 11/30/08 Period 13 12/01/08 12/28/08 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT: Help to use this to find min date in range for criter | Excel Discussion (Misc queries) | |||
| find sum if one col = ? within specific date range | Excel Discussion (Misc queries) | |||
| Find category value based on date range? | Excel Worksheet Functions | |||
| Find the earliest date in a range of dates? | Excel Worksheet Functions | |||
| Need to find Min value based on date range entered | Excel Worksheet Functions | |||
Linear Mode
