Need to search within date range within formula.

**Intermedi** · 11-25-2022, 06:28 AM

Apologies for the day 1 spam of questions...

I've just had a formula fixed for me on here but I've run into another problem; I've realised I need to apply the condition between a range of dates rather than doing greater or less than, otherwise every date after the due date will have the cost applied even if it isn't an annual one. So what I'm looking for is something like:
=if('site1!'B2=DateValue("01/09/2023-31/09/2024"),'site1!'V2,0'). I'm assuming I need to use something other than an equals sign to indicate the range or not a dash between the start and end dates, but I'm note sure what.

UK Date Formatting is applied, if that helps.

**Limor_OP** · 11-25-2022, 06:53 AM

Hi,
I dont think there is a function in excel that will do 'between' range (unlike SQL)
you will have to use >=, <= to indicate the range.

something like that:
if(and('site1!'B2>=DATE(2023,9,1),'site1!'B2<=DATE(2024,9,31)),'site1!'V2,0')

**TMS** · 11-25-2022, 07:06 AM

Try like this:

Formula:

Please Login or Register  to view this content.

Note that September only has 30 days. EOMONTH saves you having to work it out (or remember it)

**Intermedi** · 11-25-2022, 07:07 AM

It's coming up with a red dotted band around the formula box now. Current layout:

=IF('Site1!U2>=date(1/9/2023)<=DATE(31/8/2024),(Site1!V2,0). If I put a comma between the two date ranges it thinks the second one is the 'if true' function.

**TMS** · 11-25-2022, 07:11 AM

Using DATE as opposed to DATEVALUE is probably a better option. There can be no doubt about year, month and day which is a possibility in Regional variations.

**sandy666** · 11-25-2022, 07:15 AM

I don't know what are you trying to achieve in your formula but

dateanddatevalue.png

**TMS** · 11-25-2022, 07:15 AM

You have a single quote in front of the first Site.

If a sheet name has spaces in it, you need single quotes around it. If it doesn't, you don't. So, Site!U2 or 'This Site'!U2

**TMS** · 11-25-2022, 07:18 AM

And you are missing the AND.

Go with one of the options given … noting the difference in format between DATE and DATEVALUE as Sandy has pointed out. Not forgetting how many days there are in each month … I see you have changed from September to August; was that intentional?

**Intermedi** · 11-25-2022, 07:21 AM

Okay, had a crack with both. Still getting the red line. I'm assuming I've missed out something I just can't see. Current working below:

=if(AND('Site1'!U2>=DATE("01/09/2023"),'Site1'!U2<=EOMONTH(DATE("31/08/2024"),0)),'Site1'!V2,0)

When I click on the site 1 up to the date I get the 'logical one' highlight, the second site 2 gets 'logical 2', clicking on the 'eomonth' brings up the date highlight, but after that it's as though the other two functions aren't recognised as being part of the same formula.

Yes I changed to August - apologies, the range I'm looking to apply runs between the beginning of our year to end (1st September - 31st August).

Edit: Forgot to add - I'm using 'Site 1' right now to maintain the anonymity of where I'm working but eventually this will be changed to the site name which has spaces in it.

**sandy666** · 11-25-2022, 07:24 AM

You didn't read post#6

Originally Posted by Intermedi

=if(AND('Site1'!U2>=DATE("01/09/2023"),'Site1'!U2<=EOMONTH(DATE("31/08/2024"),0)),'Site1'!V2,0)

**Intermedi** · 11-25-2022, 07:26 AM

Hi Sandy - my formatting is UK rather than US so I'm using the 'DATE' function as day, month, year. Is DATEVALUE better for this? I got a bit confused as I don't have 'text'.

**sandy666** · 11-25-2022, 07:29 AM

function DATE using year, month, day doesn't matter where you are
DATE cannot use text date

**TMS** · 11-25-2022, 07:37 AM

@Intermedi: please pay attention to the detail

**sandy666** · 11-25-2022, 07:46 AM

try

Formula:

Please Login or Register  to view this content.

AND contains 0 so it will always be FALSE

**Intermedi** · 11-25-2022, 07:52 AM

I've got it working (curiously with a date/month/year format!), thank you both for your help, and your patience!

**sandy666** · 11-25-2022, 07:56 AM

You are welcome

Excel has some rules and sometimes it's worth following them
if you have problem with functions use

funsarg.png

**TMS** · 11-25-2022, 09:46 AM

Either of these should do what you want.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

The first is, perhaps, easier to read although Regional differences may confuse day/month with month/day.

The second is shorter and is a fixed format … year, month, day … wherever you are in the world.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Need to search within date range within formula.

LinkBack

Thread Tools

Rate This Thread

Display

Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Re: Need to search within date range within formula.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to search by contract number and between date range

[SOLVED] Formula help - search fixed table for corresponding date range

Search large date range with narrow filetered range

[SOLVED] formula to search text from another sheet and add the sum based on a date range

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

[SOLVED] Don't understand & sign for date range search in formula, is this correct?

Formula to search for today's date and return range of data

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions