Hi,
This has been driving me crazy... hopefully someone out there can help.
If I have an ORIGINAL range of dates in cells A1:A5:
22-Apr-04
30-Apr-04
22-May-04
22-Aug-04
22-Sep-04
and a TARGET date of say 16-Jun-04 in cell B1.
I would like to have a FUNCTION that will 'read' the OriginalDates, take the TargetDate into account, and then just INCLUDE or INSERT the target date among this original range of dates. The function would return the new dates in ascending order (i.e. with dates closest to now coming first and dates furthest from now coming last). The function would have to be typed in as an ARRAY function (with the { } bracketing the formula and closing it using Ctrl+Shift+Enter) and would return 1 more row than what is passed through OriginalDates argument.
so, the function would have two arguments and go something like this:
{=IncludeDate(OriginalDates,TargetDate)}
or;
{=(A1:A5,B1)}
In our example, the function would return the following sorted set of dates (with the June 16, 2004 date included and SORTED among the original set of dates):
22-Apr-04
30-Apr-04
22-May-04
*16-Jun-04*
22-Aug-04
22-Sep-04
in cells C1:C6 as a NEW OUTPUT RANGE of dates.
In this case, we would copy our function down 6 rows (one more than the OriginalDates) and close it with the Ctrl+Shift+Enter to make it an ARRAY of 6 rows.
A MACRO WILL NOT HELP IN THIS CASE. ALSO, USING THE DATA --> SORTING FUNCTIONALITY WILL NOT HELP IN THIS CASE. TRUE, BOTH WILL WORK, BUT THIS PROJECT CALLS FOR A **FUNCTION** TO HANDLE THE TASK.
Any ideas on how to go about doing this?
Thank you very much!
~ Monica
This has been driving me crazy... hopefully someone out there can help.
If I have an ORIGINAL range of dates in cells A1:A5:
22-Apr-04
30-Apr-04
22-May-04
22-Aug-04
22-Sep-04
and a TARGET date of say 16-Jun-04 in cell B1.
I would like to have a FUNCTION that will 'read' the OriginalDates, take the TargetDate into account, and then just INCLUDE or INSERT the target date among this original range of dates. The function would return the new dates in ascending order (i.e. with dates closest to now coming first and dates furthest from now coming last). The function would have to be typed in as an ARRAY function (with the { } bracketing the formula and closing it using Ctrl+Shift+Enter) and would return 1 more row than what is passed through OriginalDates argument.
so, the function would have two arguments and go something like this:
{=IncludeDate(OriginalDates,TargetDate)}
or;
{=(A1:A5,B1)}
In our example, the function would return the following sorted set of dates (with the June 16, 2004 date included and SORTED among the original set of dates):
22-Apr-04
30-Apr-04
22-May-04
*16-Jun-04*
22-Aug-04
22-Sep-04
in cells C1:C6 as a NEW OUTPUT RANGE of dates.
In this case, we would copy our function down 6 rows (one more than the OriginalDates) and close it with the Ctrl+Shift+Enter to make it an ARRAY of 6 rows.
A MACRO WILL NOT HELP IN THIS CASE. ALSO, USING THE DATA --> SORTING FUNCTIONALITY WILL NOT HELP IN THIS CASE. TRUE, BOTH WILL WORK, BUT THIS PROJECT CALLS FOR A **FUNCTION** TO HANDLE THE TASK.
Any ideas on how to go about doing this?
Thank you very much!
~ Monica
