You are here: HomeExcelTutorials

# Formula to find the date closest to today.

## Formula to find the date closest to today.

Price: FREE

I have a list of dates in row 1, from B1:BK1. Most of them are Sundays, but a
few are in the middle of the week. I need a formula that I can enter into another sheet to find the date from this list that is closest to today(). I found an array formula that will work, I hope to be able to do this without the array, I do not want to add any more data to the sheets, if possible.

Visit publisher's web-site: Formula to find the date closest to today.

Category and Tags: ExcelTutorials :

## Related Tutorials

### Monday closest to today's date?

Free: Yes
Tags: Excel ›

I am seeking a formula that would return the date of the closest Monday to today's date. The Monday would have to be the first one in the future, not the past. So today, Tuesday March 16, 2010, the formula would return Monday, March 22, 2010

### Array Formula Error

Free: Yes
Tags: Excel ›

I have a data set similar to below
Date Field 1 Field 2 Field 3
1/1/09 x x o
1/5/09 o x x
1/14/09 o o x
I also have an array formula in a separate cell that looks for the row with the closest match to today's date and is supposed to return the row number that is the closest match. The formula is:
=MATCH(MIN(ABS(A1:A29-TODAY()),ABS(A1:A29-TODAY()),0)
I keep getting the following error in the array formula: #VALUE!
Is it because the format of the date in column A? I am not sure what I'm doing wrong.
Thanks for your time and effort.

### Array Formula Error

Free: Yes
Tags: Excel ›

I have a data set similar to below
Date Field 1 Field 2 Field 3
1/1/09 x x o
1/5/09 o x x
1/14/09 o o x
I also have an array formula in a separate cell that looks for the row with the closest match to today's date and is supposed to return the row number that is the closest match. The formula is:
=MATCH(MIN(ABS(\$A:\$A-TODAY()),ABS(\$A:\$A-TODAY()),0)
But now I receive a new error: #NUM!
All of the dates only live in Column A. I am not sure what I'm doing wrong.
Thanks for your time and effort.

### How to find the closest match meeting conditions

Free: Yes
Tags: Excel ›

I have a reference table comprising a column of dates in ascending order and a column of readings (numbers in no order). Each date has a corresponding reading eg 5Apr11 = 100. In my worksheet I have an input date, tolerance and number eg 1Apr11, 5, 1007.
For the inputted data, I would like to show the date corresponding to the closest number in the reference table that occurs after the input date and is within the +/- tolerance.
What formula would achieve this?

### how to find address of a range of cells that contain a value closest to a given value?

Free: Yes
Tags: Excel ›

I am using Excel 2007 and I'm trying to write a formula that will locate and provide me with the address of the cells that contain values closest to my given value.
for example if my given value is 1604.and in column A I have the following values.
A1 1585
A2 1590
A3 1595
A4 1600
A5 1605
A6 1610
A7 1615
A8 1620
A9 1625
I would like a formula that returns A4:A5
"Close in value" means the two closest cells that contain a value closest to my given value. First cell that is => my given value and first cell that is =< my given value
also I should mention I would like this to be something that is done dynamically. the values in my spreadsheet are updating in real-time

## Related Applications & Scripts

### JavaScript Web Safe Colors

Free: Yes
Tags: JavaScript › Excel Miscellaneous

A small tool to aid with the selection and conversion of Web Safe Colours. Select from the 216 web safe palette or enter your own HEX/RGB colour values to find the closest match. Also converts HEX to Decimal RGB values and vice-versa.

### SupportAerie: ticketing system

Free: Yes
Tags: PHP › Excel Customer Support

SupportAerie is an open source support script. It blends the idea of a normal contact form together with a ticketing system to create the only support script you will ever need to satisfy your customers. Customers are after simplicity and an an easy to use interface, and SupportAerie can provide those necessities for the customer. A lot of the support scripts available today offer 'too much', and customers often complain about not being able to create support requests etc properly due to the complexity of today's support scripts. SupportAerie only provides the customer with what he/she needs, making SupportAerie one of the best and easiest to use support scripts of today's technological generation. SupportAerie can also generate an estimated reply date so the customer knows when he/she should expect the reply to his/her support request.

### Flip Date Clock

Free: No
Tags: Flash › Excel Clocks

This is a Flip Clock with full date description. By default is set on 12h format and with am/pm indication.
You can convert it to 24h format very easy, just follow the instructions on the ReadMeFirst.txt file you will find in the .zip archive.
Everything included in the file is vector, that means resizable to any size you desire!

Free: No
Tags: Ajax › Excel Calendars

NlsCalendar is state of the art Javascript (DHTML) AJAX enabled calendar control. Calendar is generated dynamically in browser and therefore no server request is required. It is fast and responsive, compact interface design and customizable style provides professional calendar view or date time picker for your web application.
NlsCalendar features includes basic calendar functions such as month/year navigation, flat and popup mode, any day as first day of week. In addition to that the calendar also supports month/year drop down view to quickly navigate the calendar to specific date. "Today" function allows you to quicky return to current date by single click.
Advanced features such as multiple date selection, disable date, special day and special event enhances the usability of the control. You can disable date selection, mark date as special day and define special event manually (by calendar property) or programmatically (by function). AJAX capability enrich the already long list of options available in NlsCalendar.

### Date icon component

Free: No

This is a simple component that can be added to an application showing the current date (or a selected date).
It is easily skinnable using flex css styles.