2.71 FAQ-806 How do I find the closest date/time value in a dataset?

find-closest-date

Last Update: 7/13/2018

Method 1

To find the closest date/time value in a dataset by Edit:Find menu

1. Get the internal Julian date value by date() function. For example, if you want to find the date value closest to 2004-02-01 09:39:54, get its corresponding Julian date by (Copy and paste the script in Script Window, select it and press Enter):
   

   date(2004-02-01 09:39:54.500,"yyyy''MM''dd HH':'mm':'ss'.'###")=;

2. Select Edit:Find in Sheets. Set Data Type to Numeric and enter the numeric value got in last step (Julian date getting from date() function) in Find what edit box. Modify other settings if needed. Click Find Next button.

Method 2

To find the closest date/time value in a dataset by Edit: Go to menu

1. Get the row of the closest date value by index() function like (Copy and paste the scripts in Script Window, select them and press Enter):
   

   mydate=date(2004-02-01 09:40:00.000,"yyyy'-'MM'-'dd HH':'mm':'ss'.'###");
   index(mydate,col(1),0)=;

2. Use Edit: Go to... menu to go to that row.

---

Keywords:Date, Time, Find, closest date value