+ Reply to Thread
Results 1 to 9 of 9

Certain date range - use Current year price / Certain date range - use Next year price

  1. #1
    Registered User
    Join Date
    01-27-2013
    Location
    SA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Question Certain date range - use Current year price / Certain date range - use Next year price

    Example 123.xlsx


    Hi Guys Please help me with this calculation. I have attached a basic spreadsheet to support the description

    Start Date End Date
    Current Year: 2013/09/01 2014/08/31
    Next Year: 2014/09/01 2015/08/31

    Quote Current Year Quote Next Year
    R100.00 R200.00

    G9 must check for date range B2 to C2 or
    B3 to C3 to correspond with the date manually inserted into G8

    1) I type in a certain date into G8 format dd mmmm yyyy (example 04 October 2014)
    If this date example manually typed in by me falls within the next year date range
    the value of F2 (in this case R200.00) must be inserted into G9.

    2) If G8 date value falls within the current year date range it must insert the value of E2 (in this case R100.00) into G9

    Your help will be greatly appreciated

    Kind regards
    Willie

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Certain date range - use Current year price / Certain date range - use Next year pri

    Hi and welcome to the forum

    Waar omtrent in SA? ex-pat here from PE and Klerksdorp

    Try this...
    =IF(AND($G$8>=B2,$G$8<=C2),E2,F2)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-27-2013
    Location
    SA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Certain date range - use Current year price / Certain date range - use Next year pri

    Hi daar Ford
    Ek was Vrydag in Klerksdorp met konferensie - Thaba Tshwene
    Woon in Pretoria.

    Baie dankie vir jou vriendelike hulp - Jy is 'n ster. Jou formule werk soos 'n droom!!!


    As ek jou mag vra weet jy hoe om vir Excel vra om in 'n sekere folder naam (D:/Invoices) op my pc te check en dan te kyk wat is die hoogste invoice nommer en daardie nommer +1 te allokeer as ek op 'n shape click in die nuwe invoice. Die invoice file naam is bv 123 Invoice Johan Muller (23 Feb 2014) Klerksdorp. So net die eerste paar karakters moet in ag geneem word bv as mens by 1000 Invoice Piet Pompies (10 Dec 2020) Port Elizabeth kom.

    Translation...
    How would you get excel to check a certain folder name on my PC (D:/Invoices) to find the highest invoice number and to then allocate that number +1 to the new invoice whern I click on a shape. The invoice file name is eg 123 Invoice Johan Muller (23 Feb 2014) Klerksdorp. So only the 1st few characters need to be considered, for instance if you get to 1000 Invoice Piet Pompies (10 Dec 2020) Port Elizabeth
    Last edited by FDibbins; 05-24-2014 at 08:15 PM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Certain date range - use Current year price / Certain date range - use Next year pri

    If your invoice number is in its own column, Im pretty sure we can use =MAX() for that

  5. #5
    Registered User
    Join Date
    01-27-2013
    Location
    SA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Certain date range - use Current year price / Certain date range - use Next year pri

    Hi Ford

    Thanks once again for your help


    The invoice number is in its own column in the spread sheet
    How wil I implement this to point the spreadsheet to search the folder D:/Invoices

    Kind regards
    Willie

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Certain date range - use Current year price / Certain date range - use Next year pri

    I forgot to ask if they are all in the same workbook and sheet?

    You would use teh MAX() function across workbooks in the exact same way you would across worksheets or within the same worksheet - open both workbooks, type the formula where you want the answer to be, and then use the mouse to point and highlight the range

  7. #7
    Registered User
    Join Date
    01-27-2013
    Location
    SA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Certain date range - use Current year price / Certain date range - use Next year pri

    Hi Ford

    They are all seperate workbooks - each invoice is a seperate stand alone workbook saved under it's name for example 1000 Invoice Piet Pompies (10 Dec 2020) Port Elizabeth . Hope this makes sense....
    The reason I do it this way is in TAB 1 the quote, TAB 2 The Booking form generated from TAB 1, TAB 3 The Invoice Generated from TAB 1
    I then save the entire workbook either a quote pending or as invoices once the client has confirmed

    Will the MAX() still workfor seperate workbooks?

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Certain date range - use Current year price / Certain date range - use Next year pri

    I think this will require some VBA (my weak side), so I will push this out to the other experts for you

  9. #9
    Registered User
    Join Date
    01-27-2013
    Location
    SA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Certain date range - use Current year price / Certain date range - use Next year pri

    Thanks for your help Ford ...much appreciated

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 09-04-2013, 10:49 AM
  2. Year-over-year price increases/decreases by region and category
    By psundaram in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-20-2013, 11:05 AM
  3. [SOLVED] Keeping a date as current year, even though formula determines previous year
    By dropanddrive03 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-28-2013, 08:31 PM
  4. Replies: 3
    Last Post: 01-03-2013, 12:32 AM
  5. [SOLVED] Count days for specific year falling within in multi-year date range
    By jslo2013 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-04-2012, 05:58 PM

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