Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Jamie
 
Posts: n/a
Default finding a date/time in a list that is closest to an existing date/

I have a list of dates and times. They are all on 15 min intervals. I have
another list of dates and times that are to the nearest minute.They aren't on
a regular interval so one time might be 2006-01-01 23:59 and the next might
be 2006-03-17 15:10. I want to check the dates in the first column(15min
intervals) against the second column. I would like the formula to recognize
2006-03-17 15:15 as the closest value to 2006-03-17 15:10 so that the formula
can take the data from next to the 15:10 and put it next 15:15 time.

Thanks
  #2   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default finding a date/time in a list that is closest to an existing date/

Pressuming your date columns are 52 rows long and the odd intervals are
in ascending order in column A with their data in column B and the 15
minute intervals are in column C, put the following formula in cell
D-1. If column A is not in ascending order use the Sort command from
the Data menu and be sure to sort only column B along with A.

=VLOOKUP(C1,$A$1:$D$52,2)

then copy cell cell D-1 down through D-52


ed

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to return a value between date ranges Mary-Lou Excel Worksheet Functions 7 May 26th 06 10:00 PM
finding the second last date ceemo Excel Discussion (Misc queries) 4 April 20th 06 07:52 PM
List created from XML Data, how to shift existing cells down? Matthias Excel Discussion (Misc queries) 0 March 10th 06 11:21 AM
How can I sum information in a list with a date range? Dave Excel Worksheet Functions 2 November 23rd 04 09:17 PM
Creating a list from an existing list. Jad Excel Worksheet Functions 1 October 29th 04 06:00 AM


All times are GMT +1. The time now is 07:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"