+ Reply to Thread
Results 1 to 7 of 7

Formula to Find R

  1. #1
    Forum Contributor
    Join Date
    07-10-2022
    Location
    USA
    MS-Off Ver
    2021
    Posts
    190

    Formula to Find R

    Hi,

    I have timestamps in column A starting from A2 onward. In
    Last edited by rexcel548562; 08-03-2022 at 09:53 AM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Formula to Find Respective values of a Date Stamp

    It sounds like a pretty basic filter operation (return all records with date later than target) using either the built in filtering tools or the FILTER() function

    Autofilter tutorial: https://www.wikihow.com/Use-AutoFilter-in-MS-Excel
    FILTER() function helpfile: https://support.microsoft.com/en-us/...c-4877ad80c759

    The real problem in your spreadsheet is that your "dates" in column A are stored as text strings rather than real dates. You might try a find/replace where you find the "." characters and replace with "/" and see if Excel will recognize the dates as numbers correctly. If they are still text, then you can use a Text to Columns command to specify YMD dates and hopefully coax Excel to store these dates correctly.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    07-10-2022
    Location
    USA
    MS-Off Ver
    2021
    Posts
    190

    Re: Formula to Find Respective values of a Date Stamp

    Hi Sir MrShorty,

    I am New in Excel and have very little knowledge about it.

    From Cell B2 onward, please use a suitable formula to convert this unusual date format in column A into a suitable date format.

    I want that I just write a date in Cell J2 and respective values should show in cells J4:L13

    Please try to achieve this task using a suitable Excel Formula

    I will be really very thankful to you

  4. #4
    Forum Contributor
    Join Date
    07-10-2022
    Location
    USA
    MS-Off Ver
    2021
    Posts
    190

    Re: Formula to Find Respective values of a Date Stamp

    Hi Sir MrShorty,

    I tried these two links but i got some other result which i don't need. It is due to my lack of knowledge of how to use these.

    Please see this new attached file. I have changed the dates in column A into a normal format.

    Now please make a formula that meets my needs. And share with me the Excel File
    Last edited by rexcel548562; 07-16-2022 at 12:09 PM.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Formula to Find Respective values of a Date Stamp

    Help file for Find and Replace: https://support.microsoft.com/en-us/...f-fdb42f892e90

    When you use the DATEVALUE() function, Excel refers to your operating system's date format settings (in regional and language settings) to understand what a date "looks like." I doubt your OS settings are set to any kind of YMD date format, so DATEVALUE() is not useful (without more involved text manipulation to get the date into a more common MDY format). I find the Text to Columns method (a text to columns tutorial: https://www.howtogeek.com/407217/how...-an-excel-pro/ ) superior because, at step 3 of the text import wizard, you can select the date column and specify that the date is YMD. Usually Excel doesn't automatically recognize period "." as a year/month/day delimiter, so that's why you need to substitute "/" or "-", since Excel is more likely to recognize those characters as delimiters for dates.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula to Find Respective values of a Date Stamp

    Using your original sheet:

    =INDEX(D$2:D$1510,MATCH($I4,DATEVALUE(SUBSTITUTE($A$2:$A$1510,".","/")),0))

    or using the modified file:

    =INDEX(D$2:D$1510,MATCH($I4,$A$2:$A$1510,0))

    both copied across and down.

    In your original sheet, the formula will probably be an array formula.

    If the formulae in the attached original are enclosed within a pair of { }, these ARE array formulae.

    These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    If you have a LOT of data, use th eapproach in the modified sheet. It'll be quicker.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  7. #7
    Forum Contributor
    Join Date
    07-10-2022
    Location
    USA
    MS-Off Ver
    2021
    Posts
    190

    Re: Formula to Find Respective values of a Date Stamp

    Hi Sir Glenn Kennedy,

    Thank you so much. It worked.

+ 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. Find Top x Values and respective row/column titles
    By hmltnangel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-28-2018, 12:18 PM
  2. Replies: 0
    Last Post: 08-02-2017, 10:38 AM
  3. [SOLVED] Copy Values not Formulas and Date Stamp It.
    By majime01 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2017, 07:52 AM
  4. Construct a formula so that where one enters a date, a certain respective number appears
    By PivotTablePSHomage in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-24-2016, 09:33 PM
  5. [SOLVED] Find cell value in another workbook and place date stamp in row
    By cladinshadows in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-23-2013, 04:25 PM
  6. Automatically Save and Copy Values Daily with a Date Stamp
    By cckal in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-10-2010, 05:36 PM
  7. if todays date then stamp a set of values.
    By TypeR in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-16-2007, 06:15 AM

Tags for this Thread

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