Returning $ value if order placed between two date (fiscal start and today)

Afternoon All.

I am looking to add a year to date comparison to one of my reports and seem to have gotten myself stuck!
Ideally i would like to return the order value in one cell is the date it was ordered falls between the fiscal start and today date in the corresponding last fiscal year..

A1 - i have the fiscal year start date - YYYY-MM-DD
A2 - i have the matching date from last year - YYYY-MM-DD ( today is 2018-09-13 so the date i'm looking for is 2017-09-13)

A5 - i want to return the order $ value if the date of order falls between the date range in cells A1 and A2.

Hopefully this makes sense.
Thanks in advance
Ian

I recommend attaching a small representative sample workbook along with the desired result (which you can enter manually) based on that sample.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

PLEASE SEE THE ATTACHED SAMPLE
Note this was simply made up to assist in my problem...the format of the report the formula will be applied to is a little more complex and contains confidential data.

Again thanks in advance.

ian

Hi,

Your written description does Not match your uploaded sample, you have results highlighted in your sample that is Exactly the Opposite of your written description, please clarify.

You are correct.

please assume the fiscal date is not 2018 but actually 2017. i believe then my description matches the attachment.
note that the yellow highlighted cells are what i am looking for but pulled with formulas and not manually.

thanks
ian

please assume the fiscal date is not 2018 but actually 2017

I assume that you are saying to change B2 from 6/1/2018 to 6/1/2017.

After making that change, try this in B7:

=IF(AND(D7>=B$2,D7<=B$4),H7,0)

Ok,

So I've changed B2 to 6/1/2017 and used this formula in B7 copied down:

Formula:

Please Login or Register  to view this content.

Is this what you mean, see attached.

If the date in B2 was supposed to be 6/1/2018 and you want to pull all values where the dates are between 6/1/2017 and 9/13/2017, you can use this:

B7 =IF(AND(D7>=EDATE(B$2,-12),D7<=B$4),H7,0)

thank you all..

i am still getting a result a 0... but i am thinking its he format of my data rather than your suggested formulas..

appreciate the assisstance

ian

You are getting a result of 0 down the entire column B of the sample that you shared in post #3 after following posts # 6, 7, or 8?

Apologies,
no on the test file i am getting the results i am looking for.. once transferred to my main project report i am getting the 0 result.. but i believe this is down to the formatting of the dates generated from our order management system.. i am looking into this now and will hopefully correct the issues on this side.. i don't want to make post as solved yet just in case i still need assistance on the issue.

thanks
ian

Understood.

There are a few ways to tackle this. Here are two:

1) Convert the text dates into actual (Excel recognized) dates then use the formulas as given
2) Adjust the formulas to account for the text dates

Thank you all for the assistance, i corrected the formatting of the date coming from our system and the formula works perfectly!!

have a great day
Ian

You're welcome. Glad we could help.