| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to Return a Date Value Q
I looking for a function that will return the value that is in one of
5 cells that is matches the closest to Todays date. Thus - I have 5 date values in cells A5 to E5 Todays date is in A1 In A10 I want to return the value of one of A5-E5 that is closest to todays date that is in A1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to Return a Date Value Q
"Seanie" wrote:
I looking for a function that will return the value that is in one of 5 cells that is matches the closest to Todays date. Thus - I have 5 date values in cells A5 to E5 Todays date is in A1 In A10 I want to return the value of one of A5-E5 that is closest to todays date that is in A1 Depends on what you mean by "closest". Perhaps: =LOOKUP(A1,A5:E5) formatted as Date. But that returns the closest date that is the same or earlier. So if B5 contains 3/1/2012 (March 1), C5 contains 4/1/2012 and A1 contains 3/31/2012, LOOKUP will return 3/1/2012, not 4/1/2012. Okay? Note: LOOKUP requires that A5:E5 be in ascending order. Okay? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to Return a Date Value Q
On Monday, June 25, 2012 4:14:27 PM UTC+1, joeu2004 wrote:
"Seanie" wrote: I looking for a function that will return the value that is in one of 5 cells that is matches the closest to Todays date. Thus - I have 5 date values in cells A5 to E5 Todays date is in A1 In A10 I want to return the value of one of A5-E5 that is closest to todays date that is in A1 Depends on what you mean by "closest". Perhaps: =LOOKUP(A1,A5:E5) formatted as Date. But that returns the closest date that is the same or earlier. So if B5 contains 3/1/2012 (March 1), C5 contains 4/1/2012 and A1 contains 3/31/2012, LOOKUP will return 3/1/2012, not 4/1/2012. Okay? Note: LOOKUP requires that A5:E5 be in ascending order. Okay? Thanks the 'same or earlier' doesn't really work for me, as the closest might be greater than todays date |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to Return a Date Value Q
Hi Sean,
Am Mon, 25 Jun 2012 10:55:06 -0700 (PDT) schrieb : Thanks the 'same or earlier' doesn't really work for me, as the closest might be greater than todays date try: =MIN(IF(ABS(A5:E5-$A$1)=MIN(ABS(A5:E5-$A$1)),A5:E5)) and enter the formula with CTRL+Shift+Enter Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to Return a Date Value Q
On Monday, June 25, 2012 7:14:39 PM UTC+1, Claus Busch wrote:
Hi Sean, Am Mon, 25 Jun 2012 10:55:06 -0700 (PDT) schrieb : Thanks the 'same or earlier' doesn't really work for me, as the closest might be greater than todays date try: =MIN(IF(ABS(A5:E5-$A$1)=MIN(ABS(A5:E5-$A$1)),A5:E5)) and enter the formula with CTRL+Shift+Enter Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Bingo! that seems to have worked great, Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Howw can I make a function return a date in date format | Excel Programming | |||
| Function to return column where a date is entered | Excel Programming | |||
| Using TODAY function to return a date as text | Excel Discussion (Misc queries) | |||
| Need a function or formulae that will return the current date + 1 | Excel Worksheet Functions | |||
| Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
Hybrid Mode
