+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Registered User
    Join Date
    08-24-2018
    Location
    Montreal, Canada
    MS-Off Ver
    MS Office 2016
    Posts
    19

    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

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,056

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

    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"

  3. #3
    Registered User
    Join Date
    08-24-2018
    Location
    Montreal, Canada
    MS-Off Ver
    MS Office 2016
    Posts
    19

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

    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
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    05-05-2018
    Location
    Oakland, CA
    MS-Off Ver
    2016
    Posts
    483

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

    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.

  5. #5
    Registered User
    Join Date
    08-24-2018
    Location
    Montreal, Canada
    MS-Off Ver
    MS Office 2016
    Posts
    19

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

    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

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,056

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

    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)

  7. #7
    Valued Forum Contributor
    Join Date
    05-05-2018
    Location
    Oakland, CA
    MS-Off Ver
    2016
    Posts
    483

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

    Ok,

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

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Is this what you mean, see attached.
    Attached Files Attached Files

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,056

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

    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)

  9. #9
    Registered User
    Join Date
    08-24-2018
    Location
    Montreal, Canada
    MS-Off Ver
    MS Office 2016
    Posts
    19

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

    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

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,056

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

    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?

  11. #11
    Registered User
    Join Date
    08-24-2018
    Location
    Montreal, Canada
    MS-Off Ver
    MS Office 2016
    Posts
    19

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

    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

  12. #12
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,056

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

    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

  13. #13
    Registered User
    Join Date
    08-24-2018
    Location
    Montreal, Canada
    MS-Off Ver
    MS Office 2016
    Posts
    19

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

    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

  14. #14
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,056

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

    You're welcome. Glad we could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1