Identify if date falls within a date range

**Chris F** · 11-22-2016, 08:40 AM

This is a pretty basic question but I'm struggling. Have tried searching but can't find the answer.

I have 10k. properties with a date against each e.g. when the kitchen was installed in. The column just contains the date.

For a 30 year business plan I need to identify which year the kitchens will need replacement based on a 20 year life cycle. I have columns 2017/18, 2018/19 etc. for the 30 years. If the kitchen was installed in say 1997 it will require replacement in 2017/18.

Starting with 2017/18 I'm trying to identify all properties which had the kitchen replaced between 01/04/2017 and 31/03/2018 and will then change the formula for each subsequent year/column.

Thanks in advance. Chris

**Pete_UK** · 11-22-2016, 11:46 AM

It would help if you attached a sample Excel workbook, so that we can see exactly how your data is laid out (note, a sample workbook only needs about 10 or 20 records which are representative of your data).

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

**mikeTRON** · 11-22-2016, 11:50 AM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**Pete_UK** · 11-22-2016, 12:14 PM

Hey Mike,

congratulations on 3,000 posts.

Pete

**mikeTRON** · 11-22-2016, 01:15 PM

Originally Posted by Pete_UK

Hey Mike,

congratulations on 3,000 posts.

Pete

Ha thanks. I bet half of them are "please post a sample workbook."

**Jaffary** · 12-18-2016, 04:29 AM

yes, as mike, please share a worksheet.
what i understand it would be done through IF() and AND() combo-formula

**Glenn Kennedy** · 12-18-2016, 04:48 AM

You haven't given us much to go on, have you. Since your future dates look like financial years, rather than calendar years, I have assumed that you want years running from 1st April to 31 March.

Try the formula in the sheet. I can't paste it stright in today for some reason...

assuming dates are in A2 and your future year columns start from B2. If this isn't what you want - try explaining what you DO want.

And... if it's not what you want, attach a sample Excel workbook? Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**Glenn Kennedy** · 12-18-2016, 05:27 AM

Apols. You do have the date range requirement clearly identified. I can't edit my slightly carping comment out, as the Forum has developed a new feature - we can't edit our posts any more. FFS.

Hopefully the formula (which the forum wouldn't let me post, either... FFS) will work OK, though.

**AliGW** · 12-18-2016, 05:30 AM

If the formula included < or > , just add a space either side of them.

**Glenn Kennedy** · 12-18-2016, 05:31 AM

Even that doesn't always work...

**AliGW** · 12-18-2016, 05:36 AM

It does for me, I have to say.

**Chris F** · 01-26-2017, 06:18 AM

Apologies for the delay in returning to this thread.

Glen, thank you for taking the time to respond even though you hadn't got all the information you required. Unfortunately I couldn't open your attachment - it's saying it's an attachment.php file and asking me to stipulate/find a program to open it.

I've now managed to do what I wanted using =AND($S5>=U$1,$S5<=U$2)*$T5 and putting each year range e.g. 01/04/2016 31/03/2017 etc. above each column heading.

I was hoping for a formula that would return a result straight from the date data but the above works which is the important thing, so job done.

Chris

**Rlakhani000** · 02-18-2022, 04:34 PM

This helped me so much. I've been working on this same issue for 3 days. Can't thank you enough, you the man!

**Companyphone** · 02-23-2022, 08:35 AM

Hi everyone , any change you can help me with almost same issue.
looking for a formula to auto- fill cells . Looking for a formula that will auto-fill the cell in multiple columns (same row ) if they are within the date range .
So looking if date range from C6 and D6 are within the dates from J3 to CI3 to divide E6 in equal numbers in all the cells from same row and columns that are included in date range .
sorry if a bit confusing but english isn't my 1st language , i did fill them in manualy (j6-N6) .

**Glenn Kennedy** · 02-23-2022, 08:40 AM

Hi.

It's a forum rule that you start your own thread, rather than "piggy-backing" on someone else's thread. This helps prevent massive confusion arising - which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution).

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. So, prepare a SMALL sample sheet (10-20 rows, not thousands!!!). Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

The yellow banner about sample worksheets, at the top of the screen tells you how to post a sheet here on the Forum.

Identify if date falls within a date range

LinkBack

Thread Tools

Display

Identify if date falls within a date range

Re: Identify if date falls within a date range

Re: Identify if date falls within a date range

Re: Identify if date falls within a date range

Re: Identify if date falls within a date range

Re: Identify if date falls within a date range

Re: Identify if date falls within a date range

Re: Identify if date falls within a date range

Re: Identify if date falls within a date range

Re: Identify if date falls within a date range

Re: Identify if date falls within a date range

Re: Identify if date falls within a date range

Re: Identify if date falls within a date range

Re: Identify if date falls within a date range

Re: Identify if date falls within a date range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to check if a date falls within a date range, then returns a corresponding cell

[SOLVED] Formula to assign specific numbers if a date falls within a certain date range

Search date range +/- 7 days and return date that falls within range

[SOLVED] If statement to check if date falls between date range

nested IF formula to return a number if a date falls within a given date range

Mark cell if location matches and date falls between date range

How to shade a cell when a date falls within a certain date range

Bookmarks

Bookmarks

Posting Permissions