Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA TO LOCATE NEXT DATE
Hi
I'm posting this question on behalf of a colleague, so hope I have understood the question correctly. He has a spreadsheet with an ascending list of dates (column D). In column A he is wanting to show the next date. Example - Cell D2 = 7 September (07/09/2007) and the next date in the list (Cell D3) is 11 September (11/09/2007). In Cell A2, he wants to place a formula showing the next date (i.e. cell D3), allowing for the fact that on some days there may be more than 1 row with the same date, so will all need to be show in Column A. Could anyone make any suggestions and if not clear on the question, please don't hesitate to come back and ask. Many thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA TO LOCATE NEXT DATE
One play using non-array formulas which will deliver the desired dynamic
unique listing of dates in source col E .. Source dates assumed running in D2 down In A2: =IF(E2="","",IF(COUNTIF(E$2:E2,E2)1,"",ROW())) Leave A1 blank In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(E:E,SMALL(A:A,R OWS($1:1)))) Format B2 as date. Select A2:B2, copy down to cover the max expected extent of data in col E. Hide away col A. Col B will dynamically return the desired unique list of dates in col E, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ellie" wrote: Hi I'm posting this question on behalf of a colleague, so hope I have understood the question correctly. He has a spreadsheet with an ascending list of dates (column D). In column A he is wanting to show the next date. Example - Cell D2 = 7 September (07/09/2007) and the next date in the list (Cell D3) is 11 September (11/09/2007). In Cell A2, he wants to place a formula showing the next date (i.e. cell D3), allowing for the fact that on some days there may be more than 1 row with the same date, so will all need to be show in Column A. Could anyone make any suggestions and if not clear on the question, please don't hesitate to come back and ask. Many thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA TO LOCATE NEXT DATE
Thank you Max.
Unfortunately my colleague advises that I have misunderstood slightly what he requires. Where I stated that in the dates column, column D, there may be some instances occurring where the same date occurs in more than one row of that column, he has advised he is only requiring to know the first occurrence to be shown in column A. Hope this makes sense. Many thanks. Ellie "Max" wrote: One play using non-array formulas which will deliver the desired dynamic unique listing of dates in source col E .. Source dates assumed running in D2 down In A2: =IF(E2="","",IF(COUNTIF(E$2:E2,E2)1,"",ROW())) Leave A1 blank In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(E:E,SMALL(A:A,R OWS($1:1)))) Format B2 as date. Select A2:B2, copy down to cover the max expected extent of data in col E. Hide away col A. Col B will dynamically return the desired unique list of dates in col E, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ellie" wrote: Hi I'm posting this question on behalf of a colleague, so hope I have understood the question correctly. He has a spreadsheet with an ascending list of dates (column D). In column A he is wanting to show the next date. Example - Cell D2 = 7 September (07/09/2007) and the next date in the list (Cell D3) is 11 September (11/09/2007). In Cell A2, he wants to place a formula showing the next date (i.e. cell D3), allowing for the fact that on some days there may be more than 1 row with the same date, so will all need to be show in Column A. Could anyone make any suggestions and if not clear on the question, please don't hesitate to come back and ask. Many thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA TO LOCATE NEXT DATE
Where I stated that in the dates column, column D, there may be some
instances occurring where the same date occurs in more than one row of that column, he has advised he is only requiring to know the first occurrence to be shown in column A. But, but, isn't that essentially what's returned in col B in the suggested set-up? An example .. Supposing Col E (source dates) contains the following dates in E2:E8 07-09-2007 11-09-2007 11-09-2007 12-09-2007 12-09-2007 12-09-2007 13-09-2007 then Col B (unique extracts of source dates) would return it as: 07-09-2007 11-09-2007 12-09-2007 13-09-2007 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ellie" wrote: Thank you Max. Unfortunately my colleague advises that I have misunderstood slightly what he requires. Where I stated that in the dates column, column D, there may be some instances occurring where the same date occurs in more than one row of that column, he has advised he is only requiring to know the first occurrence to be shown in column A. Hope this makes sense. Many thanks. Ellie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA TO LOCATE NEXT DATE
Sorry Max, having just been advised the structure of the spreadsheet has
changed and in turn also the date basis of my earlier request, starting again: Column D contains the dates, as before. Cell A1 only is to show the next date after today's date. Sorry for the change and hope you are able to help. Ellie "Max" wrote: Where I stated that in the dates column, column D, there may be some instances occurring where the same date occurs in more than one row of that column, he has advised he is only requiring to know the first occurrence to be shown in column A. But, but, isn't that essentially what's returned in col B in the suggested set-up? An example .. Supposing Col E (source dates) contains the following dates in E2:E8 07-09-2007 11-09-2007 11-09-2007 12-09-2007 12-09-2007 12-09-2007 13-09-2007 then Col B (unique extracts of source dates) would return it as: 07-09-2007 11-09-2007 12-09-2007 13-09-2007 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ellie" wrote: Thank you Max. Unfortunately my colleague advises that I have misunderstood slightly what he requires. Where I stated that in the dates column, column D, there may be some instances occurring where the same date occurs in more than one row of that column, he has advised he is only requiring to know the first occurrence to be shown in column A. Hope this makes sense. Many thanks. Ellie |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA TO LOCATE NEXT DATE
Column D contains the dates, as before.
Cell A1 only is to show the next date after today's date. Try in A1, array-enter the formula by pressing Ctrl+Shift+Enter: =MIN(IF(D$2:D$100TODAY(),D$2:D$100)) Format A1 as date. Adjust the range to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ellie" wrote: Sorry Max, having just been advised the structure of the spreadsheet has changed and in turn also the date basis of my earlier request, starting again: Column D contains the dates, as before. Cell A1 only is to show the next date after today's date. Sorry for the change and hope you are able to help. Ellie |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA TO LOCATE NEXT DATE
Thank you, Max. Works brilliantly.
Many thanks for your patience and help and apologies for the sudden change of requirements half way through. Ellie "Max" wrote: Column D contains the dates, as before. Cell A1 only is to show the next date after today's date. Try in A1, array-enter the formula by pressing Ctrl+Shift+Enter: =MIN(IF(D$2:D$100TODAY(),D$2:D$100)) Format A1 as date. Adjust the range to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ellie" wrote: Sorry Max, having just been advised the structure of the spreadsheet has changed and in turn also the date basis of my earlier request, starting again: Column D contains the dates, as before. Cell A1 only is to show the next date after today's date. Sorry for the change and hope you are able to help. Ellie |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA TO LOCATE NEXT DATE
welcome, Ellie. glad that worked <g
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ellie" wrote in message ... Thank you, Max. Works brilliantly. Many thanks for your patience and help and apologies for the sudden change of requirements half way through. Ellie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Locate month n year from range of date | New Users to Excel | |||
Locate month and year from range of date from another sheet | New Users to Excel | |||
how do I locate a cell that provides data to a formula ? | Excel Worksheet Functions | |||
how do I locate a cell that provides data to a formula ? | Excel Worksheet Functions |