+ Reply to Thread
Results 1 to 16 of 16

IF Function and date range

  1. 06-15-2015, 06:17 AM #1
    chadduquesne
    chadduquesne is offline
    Registered User
    Join Date
    06-15-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    9

    IF Function and date range

    Hi,

    I am working on a personal finance spreadsheet and would like to know how I can calculate data within a specific day range.

    Currently in column A is the showing the data within the past year using the following formula. "X" represents data that is not withing the past year.
    =IF(D2>MAX(D:D)-365,"","X")

    I would like column B to show the data within the second year and my current solution is
    =IF(D2>MAX(D:D)-730,"","X")

    however, this solution will show data for the first year and second year.

    How do I get it to only show the second year?

    Any help is appreciated.
    Reply With QuoteRegister To Reply
  2. 06-15-2015, 06:26 AM #2
    NeedForExcel
    NeedForExcel is offline
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: IF Function and date range

    So basically you want data greater than 365 and less than 730?
    Last edited by NeedForExcel; 06-15-2015 at 06:28 AM.
    Cheers!
    Deep Dave
    Reply With QuoteRegister To Reply
  3. 06-15-2015, 06:27 AM #3
    chadduquesne
    chadduquesne is offline
    Registered User
    Join Date
    06-15-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: IF Function and date range

    Yes thats it!
    Reply With QuoteRegister To Reply
  4. 06-15-2015, 06:28 AM #4
    NeedForExcel
    NeedForExcel is offline
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: IF Function and date range

    Something like this?

    =IF(AND(D2>MAX(D:D)-365,D2<=MAX(D:D)-730),"","X")
    Last edited by NeedForExcel; 06-15-2015 at 06:32 AM.
    Reply With QuoteRegister To Reply
  5. 06-15-2015, 06:42 AM #5
    chadduquesne
    chadduquesne is offline
    Registered User
    Join Date
    06-15-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: IF Function and date range

    mmmmm, Seems to have worked for year 3 but not for year 2?

    2015-06-15_2040.png
    Reply With QuoteRegister To Reply
  6. 06-15-2015, 06:45 AM #6
    NeedForExcel
    NeedForExcel is offline
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: IF Function and date range

    Your formula is referring to cell E2 in cell C2.. Is that what you want?

    If its right, please attach a sample workbook.. Its easier to work with it..
    Reply With QuoteRegister To Reply
  7. 06-15-2015, 06:54 AM #7
    chadduquesne
    chadduquesne is offline
    Registered User
    Join Date
    06-15-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: IF Function and date range

    test.xlsx

    Thanks
    Reply With QuoteRegister To Reply
  8. 06-15-2015, 07:06 AM #8
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: IF Function and date range

    How about a pivot table.

    I used 3 helpcolumns to determine the year, month, day.

    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  9. 06-15-2015, 07:25 AM #9
    NeedForExcel
    NeedForExcel is offline
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: IF Function and date range

    I'd use a Pivot Table too..
    Reply With QuoteRegister To Reply
  10. 06-15-2015, 07:47 AM #10
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,937

    Re: IF Function and date range

    See the attached file (i am not sure)
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  11. 06-16-2015, 05:38 AM #11
    chadduquesne
    chadduquesne is offline
    Registered User
    Join Date
    06-15-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: IF Function and date range

    Thanks for all the information. i do like the idea of a pivot table as oeldere had created, however I do like the formula I had created above which automatically shows a rolling 12 months data.
    Reply With QuoteRegister To Reply
  12. 06-16-2015, 05:40 AM #12
    chadduquesne
    chadduquesne is offline
    Registered User
    Join Date
    06-15-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: IF Function and date range

    thats exactly what Im after!

    Thanks nflsales really appreciated
    Reply With QuoteRegister To Reply
  13. 06-16-2015, 06:14 AM #13
    chadduquesne
    chadduquesne is offline
    Registered User
    Join Date
    06-15-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: IF Function and date range

    Guys, im back again.

    Can anyone have a look at the attached? My formulas dont seem to be working.

    Basically i input all the data in the "data input" sheet and then go to the "summary sheet" and refresh all the pivot tables. however the issue is the formulas are not working for feb to current on the "data input" sheet?

    Alana & Chad Budget Template 2015 v2.xlsx
    Reply With QuoteRegister To Reply
  14. 06-16-2015, 06:29 AM #14
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: IF Function and date range

    Filter on all values in cel B2.

    See the attached file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  15. 06-16-2015, 06:34 AM #15
    chadduquesne
    chadduquesne is offline
    Registered User
    Join Date
    06-15-2015
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    9

    Re: IF Function and date range

    Thanks but why do cells A86, B86 and C86 and down show "#NUM!" ? shouldnt it be clear if its within the date and display "X" if not?
    Reply With QuoteRegister To Reply
  16. 06-16-2015, 07:08 AM #16
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: IF Function and date range

    First of all, I believe the syntax of the formula, needs to change.

    If i read the syntax it has to be, (old date, new date, month).

    Then you also have to change the formula (change D42 in D86, since you don't only want the data of january (i suppose).

    See the yellow en green cells in the attached file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. IF function finding a date in a date range
    By KelleyScott in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-29-2015, 06:26 AM
  2. a function to find the previous date based on a given date and a range of dates
    By scott.s.fower in forum Tips and Tutorials
    Replies: 0
    Last Post: 05-30-2013, 07:50 PM
  3. Function that will create a range based on a start date and end date
    By ckosman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-24-2013, 10:09 PM
  4. [SOLVED] SUMIF within date range as a function of today()'s date
    By irvine79 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-06-2006, 01:00 PM
  5. [SOLVED] MAX figure within a date range as a function of today()'s date
    By irvine79 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-06-2006, 12:45 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1