| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Period Date?
Given a particular date, is there a way that I can distinguish what period it
falls between? I plan on a using an if function combined with another formula. What period is 9/1/2008? The answer should be Period 9. 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 Period Date?
Hi,
With the date you want to find the period for in D1 =INDEX(A1:A12,MATCH(1,INDEX((B1:B12<=D1)*(C1:C12= D1),),)) Mike "MrRJ" wrote: Given a particular date, is there a way that I can distinguish what period it falls between? I plan on a using an if function combined with another formula. What period is 9/1/2008? The answer should be Period 9. 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 Period Date?
If you move the periods to the last column:
12/31/2007 1/27/2008 Period 1 1/28/2008 2/24/2008 Period 2 2/25/2008 3/23/2008 Period 3 3/24/2008 4/20/2008 Period 4 4/21/2008 5/18/2008 Period 5 5/19/2008 6/15/2008 Period 6 6/16/2008 7/13/2008 Period 7 7/14/2008 8/10/2008 Period 8 8/11/2008 9/7/2008 Period 9 9/8/2008 10/5/2008 Period 10 10/6/2008 11/2/2008 Period 11 11/3/2008 11/30/2008 Period 12 12/1/2008 12/28/2008 Period 13 Then VLOOKUP() can be used: =VLOOKUP(DATEVALUE("9/1/2008"),A1:C13,3) -- Gary''s Student - gsnu200816 "MrRJ" wrote: Given a particular date, is there a way that I can distinguish what period it falls between? I plan on a using an if function combined with another formula. What period is 9/1/2008? The answer should be Period 9. 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 Period Date?
Thanks Gary,
Have a nice holiday! "Gary''s Student" wrote: If you move the periods to the last column: 12/31/2007 1/27/2008 Period 1 1/28/2008 2/24/2008 Period 2 2/25/2008 3/23/2008 Period 3 3/24/2008 4/20/2008 Period 4 4/21/2008 5/18/2008 Period 5 5/19/2008 6/15/2008 Period 6 6/16/2008 7/13/2008 Period 7 7/14/2008 8/10/2008 Period 8 8/11/2008 9/7/2008 Period 9 9/8/2008 10/5/2008 Period 10 10/6/2008 11/2/2008 Period 11 11/3/2008 11/30/2008 Period 12 12/1/2008 12/28/2008 Period 13 Then VLOOKUP() can be used: =VLOOKUP(DATEVALUE("9/1/2008"),A1:C13,3) -- Gary''s Student - gsnu200816 "MrRJ" wrote: Given a particular date, is there a way that I can distinguish what period it falls between? I plan on a using an if function combined with another formula. What period is 9/1/2008? The answer should be Period 9. 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 Period Date?
Thanks Mike H.
Have a nice holiday! "Mike H" wrote: Hi, With the date you want to find the period for in D1 =INDEX(A1:A12,MATCH(1,INDEX((B1:B12<=D1)*(C1:C12= D1),),)) Mike "MrRJ" wrote: Given a particular date, is there a way that I can distinguish what period it falls between? I plan on a using an if function combined with another formula. What period is 9/1/2008? The answer should be Period 9. 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find Period Date?
Your welcome. What holiday?
"MrRJ" wrote: Thanks Mike H. Have a nice holiday! "Mike H" wrote: Hi, With the date you want to find the period for in D1 =INDEX(A1:A12,MATCH(1,INDEX((B1:B12<=D1)*(C1:C12= D1),),)) Mike "MrRJ" wrote: Given a particular date, is there a way that I can distinguish what period it falls between? I plan on a using an if function combined with another formula. What period is 9/1/2008? The answer should be Period 9. 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 | |||
| Assign period to date based on rules. | Excel Worksheet Functions | |||
| Calculate a date based on period and year | Excel Worksheet Functions | |||
| Period to date | Excel Discussion (Misc queries) | |||
| Match name and Date Period for % | Excel Discussion (Misc queries) | |||
| Sum column based on value in each row, if two cells equal, or if date is within time period | Excel Worksheet Functions | |||
Linear Mode
