Hi,
I have timestamps in column A starting from A2 onward. In
Hi,
I have timestamps in column A starting from A2 onward. In
Last edited by rexcel548562; 08-03-2022 at 09:53 AM.
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.
Originally Posted by shg
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
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.
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.
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.
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
Hi Sir Glenn Kennedy,
Thank you so much. It worked.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks