+ Reply to Thread
Results 1 to 3 of 3

According to the date in column B, it will display 1 if it falls within the time range

  1. #1
    Forum Contributor
    Join Date
    09-01-2020
    Location
    bangladesh
    MS-Off Ver
    office 2007
    Posts
    167

    According to the date in column B, it will display 1 if it falls within the time range

    Example

    B9 Date 2021/7/19 It is only 2 days from that day to today, so D9 automatically displays 1, and E9:J9 is blank

    Can someone please help me ? It would be much appreciated. Thanks in advance
    Attached Files Attached Files
    Last edited by rayhen; 07-21-2021 at 06:48 PM.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: According to the date in column B, it will display 1 if it falls within the time range

    this is how I might approach it, using a helper row that you can hide if you want, I moved your data down one row and the helper row is in row1. I put in the numeric values for your ranges, 31 for within a month, 182.6 for greater than 1 month and less than or equal to 6 months, etc.
    then used three different formulas. First in D3 (remember your data was bumped down a row)... =IF(TODAY()-$B3<D$1,1,"")
    then in E3... =IF(AND(TODAY()-$B3<E$1,TODAY()-$B3>=D$1),1,"") This one you can drag to the right over to column I.
    Then in J3 this one... =IF(TODAY()-$B3>=J$1,1,"")

    Here it is in your attached again.
    Attached Files Attached Files
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Contributor
    Join Date
    09-01-2020
    Location
    bangladesh
    MS-Off Ver
    office 2007
    Posts
    167

    Re: According to the date in column B, it will display 1 if it falls within the time range

    @Sam Capricci Excellent solution. Thank you very much.

+ 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: 17
    Last Post: 07-19-2018, 07:42 AM
  2. If Date and Time falls between Range, SUM and copy to Results Page
    By bcarroll76 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-28-2017, 02:56 PM
  3. Replies: 5
    Last Post: 03-02-2015, 05:57 PM
  4. [SOLVED] Is there a way to display the week that a date falls within the range
    By john dalton in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2013, 07:57 AM
  5. If # falls in between a range then I want it to display a %
    By sld1002 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2013, 04:26 PM
  6. Display the date and time each time the data in a cell range is changed
    By BVZM in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-24-2012, 01:23 PM
  7. Replies: 1
    Last Post: 07-19-2006, 09:40 AM

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