| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
getting the next date based on another date
I have a start date in one cell and a list of dates in another part of my
worksheet. I want to get the next date after the start date from the list of dates to populate into another cell. ie Start date is July 3 and the next date after that is the second in the list of dates (July 12). I've tried LOOKUP but keep on getting the first date from the list (June 12). -- Fay |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
getting the next date based on another date
Are the dates in ascending order?
=INDEX(A2:A50,MATCH(B2,A2:A50,0)+1) where B2 is the starting date if not sorted you can use =INDEX(A2:A50,MATCH(SMALL(A2:A50,COUNTIF(A2:A50,"< ="&B2)+1),A2:A50,0)) the latter assumes you always want the date greater than the date in B2, the former simply gets the next date in sorting order as long the date in B2 is found and it is not the last date of the list -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "Fay" wrote in message ... I have a start date in one cell and a list of dates in another part of my worksheet. I want to get the next date after the start date from the list of dates to populate into another cell. ie Start date is July 3 and the next date after that is the second in the list of dates (July 12). I've tried LOOKUP but keep on getting the first date from the list (June 12). -- Fay |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
getting the next date based on another date
I've tried this and I get N/A in the cell. To further clarify, my start date
is in cell H13 and is entered by the user. Value = July 3, 2007 The orientation dates are in cells A85:A92 and the values a June 12, 2007 July 14, 2007 August 15, 2007 September 13, 2007 October 12, 2007 November 12, 2007 December 14, 2007 I'm wanting to return the value from A85:A92 into H23 that is the next date after the start date. For the example the returned value would be July 14, 2007. If the start date is October 22 then the returned value should be November 12, 2007. Thanks for any additional insight you can give me. -- Fay "Peo Sjoblom" wrote: Are the dates in ascending order? =INDEX(A2:A50,MATCH(B2,A2:A50,0)+1) where B2 is the starting date if not sorted you can use =INDEX(A2:A50,MATCH(SMALL(A2:A50,COUNTIF(A2:A50,"< ="&B2)+1),A2:A50,0)) the latter assumes you always want the date greater than the date in B2, the former simply gets the next date in sorting order as long the date in B2 is found and it is not the last date of the list -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "Fay" wrote in message ... I have a start date in one cell and a list of dates in another part of my worksheet. I want to get the next date after the start date from the list of dates to populate into another cell. ie Start date is July 3 and the next date after that is the second in the list of dates (July 12). I've tried LOOKUP but keep on getting the first date from the list (June 12). -- Fay |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
getting the next date based on another date
This worked for me using your example
=INDEX(A85:A92,MATCH(SMALL(A85:A92,COUNTIF(A85:A92 ,"<="&H13)+1),A85:A92,0)) note that the H23 needs to be formatted as a date or you will get 39277 -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "Fay" wrote in message ... I've tried this and I get N/A in the cell. To further clarify, my start date is in cell H13 and is entered by the user. Value = July 3, 2007 The orientation dates are in cells A85:A92 and the values a June 12, 2007 July 14, 2007 August 15, 2007 September 13, 2007 October 12, 2007 November 12, 2007 December 14, 2007 I'm wanting to return the value from A85:A92 into H23 that is the next date after the start date. For the example the returned value would be July 14, 2007. If the start date is October 22 then the returned value should be November 12, 2007. Thanks for any additional insight you can give me. -- Fay "Peo Sjoblom" wrote: Are the dates in ascending order? =INDEX(A2:A50,MATCH(B2,A2:A50,0)+1) where B2 is the starting date if not sorted you can use =INDEX(A2:A50,MATCH(SMALL(A2:A50,COUNTIF(A2:A50,"< ="&B2)+1),A2:A50,0)) the latter assumes you always want the date greater than the date in B2, the former simply gets the next date in sorting order as long the date in B2 is found and it is not the last date of the list -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "Fay" wrote in message ... I have a start date in one cell and a list of dates in another part of my worksheet. I want to get the next date after the start date from the list of dates to populate into another cell. ie Start date is July 3 and the next date after that is the second in the list of dates (July 12). I've tried LOOKUP but keep on getting the first date from the list (June 12). -- Fay |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
getting the next date based on another date
A few keystrokes shorter:
=IF(H13<A85,A85,INDEX(A85:A92,MATCH(H13,A85:A92)+1 )) The only difference is the error returned if the date in H13 is = max date in the table. You formula will return #NUM! mine will return #REF!. Or, this array formula** which will return a blank: =IF(H13=A92,"",MIN(IF(A85:A92H13,A85:A92))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Peo Sjoblom" wrote in message ... This worked for me using your example =INDEX(A85:A92,MATCH(SMALL(A85:A92,COUNTIF(A85:A92 ,"<="&H13)+1),A85:A92,0)) note that the H23 needs to be formatted as a date or you will get 39277 -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "Fay" wrote in message ... I've tried this and I get N/A in the cell. To further clarify, my start date is in cell H13 and is entered by the user. Value = July 3, 2007 The orientation dates are in cells A85:A92 and the values a June 12, 2007 July 14, 2007 August 15, 2007 September 13, 2007 October 12, 2007 November 12, 2007 December 14, 2007 I'm wanting to return the value from A85:A92 into H23 that is the next date after the start date. For the example the returned value would be July 14, 2007. If the start date is October 22 then the returned value should be November 12, 2007. Thanks for any additional insight you can give me. -- Fay "Peo Sjoblom" wrote: Are the dates in ascending order? =INDEX(A2:A50,MATCH(B2,A2:A50,0)+1) where B2 is the starting date if not sorted you can use =INDEX(A2:A50,MATCH(SMALL(A2:A50,COUNTIF(A2:A50,"< ="&B2)+1),A2:A50,0)) the latter assumes you always want the date greater than the date in B2, the former simply gets the next date in sorting order as long the date in B2 is found and it is not the last date of the list -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "Fay" wrote in message ... I have a start date in one cell and a list of dates in another part of my worksheet. I want to get the next date after the start date from the list of dates to populate into another cell. ie Start date is July 3 and the next date after that is the second in the list of dates (July 12). I've tried LOOKUP but keep on getting the first date from the list (June 12). -- Fay |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
getting the next date based on another date
Thanks very much. They all worked great. Boss is impressed as well!
-- Fay "T. Valko" wrote: A few keystrokes shorter: =IF(H13<A85,A85,INDEX(A85:A92,MATCH(H13,A85:A92)+1 )) The only difference is the error returned if the date in H13 is = max date in the table. You formula will return #NUM! mine will return #REF!. Or, this array formula** which will return a blank: =IF(H13=A92,"",MIN(IF(A85:A92H13,A85:A92))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Peo Sjoblom" wrote in message ... This worked for me using your example =INDEX(A85:A92,MATCH(SMALL(A85:A92,COUNTIF(A85:A92 ,"<="&H13)+1),A85:A92,0)) note that the H23 needs to be formatted as a date or you will get 39277 -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "Fay" wrote in message ... I've tried this and I get N/A in the cell. To further clarify, my start date is in cell H13 and is entered by the user. Value = July 3, 2007 The orientation dates are in cells A85:A92 and the values a June 12, 2007 July 14, 2007 August 15, 2007 September 13, 2007 October 12, 2007 November 12, 2007 December 14, 2007 I'm wanting to return the value from A85:A92 into H23 that is the next date after the start date. For the example the returned value would be July 14, 2007. If the start date is October 22 then the returned value should be November 12, 2007. Thanks for any additional insight you can give me. -- Fay "Peo Sjoblom" wrote: Are the dates in ascending order? =INDEX(A2:A50,MATCH(B2,A2:A50,0)+1) where B2 is the starting date if not sorted you can use =INDEX(A2:A50,MATCH(SMALL(A2:A50,COUNTIF(A2:A50,"< ="&B2)+1),A2:A50,0)) the latter assumes you always want the date greater than the date in B2, the former simply gets the next date in sorting order as long the date in B2 is found and it is not the last date of the list -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "Fay" wrote in message ... I have a start date in one cell and a list of dates in another part of my worksheet. I want to get the next date after the start date from the list of dates to populate into another cell. ie Start date is July 3 and the next date after that is the second in the list of dates (July 12). I've tried LOOKUP but keep on getting the first date from the list (June 12). -- Fay |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
getting the next date based on another date
When you say "next date" do you mean the next date that is closest to the
start date or do you mean the list dates will include the start date and you want the next date that is physically listed after the start date? Biff "Fay" wrote in message ... I have a start date in one cell and a list of dates in another part of my worksheet. I want to get the next date after the start date from the list of dates to populate into another cell. ie Start date is July 3 and the next date after that is the second in the list of dates (July 12). I've tried LOOKUP but keep on getting the first date from the list (June 12). -- Fay |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct based which also weights data based on date | Excel Worksheet Functions | |||
| to-date total based on current date | Excel Worksheet Functions | |||
| Finding the Monday date based on a different date in same week | Excel Worksheet Functions | |||
| Autofill date based on date in another cell | Excel Worksheet Functions | |||
| Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions | |||
Hybrid Mode
