Calculate the earliest or latest date in Excel based on criteria by Chris Menard
ฝัง
- เผยแพร่เมื่อ 19 ก.พ. 2019
- If you have dates in Excel, and you want to find the earlies or lastest date based on a criteria, use the MAX or MIN function with the IF function. The formula will need to be entered as an array function
And make sure you subscribe to my channel!
- EQUIPMENT USED --------------------------------
○ My camera - amzn.to/3vdgF5E
○ Microphone - amzn.to/3gphDXh
○ Camera tripod - amzn.to/3veN6Rg
○ Studio lights - amzn.to/3vaxyy5
○ Dual monitor mount stand - amzn.to/3vbZSjJ
○ Web camera - amzn.to/2Tg75Sn
○ Shock mount - amzn.to/3g96FGj
○ Boom Arm - amzn.to/3g8cNi6
- SOFTWARE USED --------------------------------
○ Screen recording - Camtasia - chrismenardtraining.com/camtasia
○ Screenshots - Snagit - chrismenardtraining.com/snagit
○ TH-cam keyword search - TubeBuddy - www.tubebuddy.com/chrismenard
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week! - แนวปฏิบัติและการใช้ชีวิต
This is way simpler than everything I was trying, thank you for posting@😁
Greetings frm Malaysia.!!
Thank you so much Chris!, easy to understand and ease my works
Thank you Nur from Malaysia!
I was looking for exactly this! Thanks so much Chris.
Glad it was helpful! Thanks, Rachit.
Very useful formula👍🏻, helped me saving lot of time.
you're a hero. this is awesome. saved me hours and hours of work. Thanks
You're welcome!
Yeee it worked precisely to my working projects. This particular lesson has made my project looked genius 🙏 🤙 😁 👏
Great to hear! Thank you, Dino.
Thank you Sir.
You have saved my week. Now I can complete week task in an hour.
Happy to help
Thanks for the video. I would like to expand on it a little to solve a problem. Is it possible to return the last value if meeting 2 criteria?
Thank so much for this simple way to share this.. I have subscribed you for more these types of videos to improve my skill.
This is just what i needed thank you. But i did realize thta you must be the $ in your formula otherwise when you drag the formula it will not populate the correct date. Just wanted to share because you don't always need that and i don't usually use the Max formula so i didn't know.
Awesome video and great use of formulae and array function!
Thank you!
Wow, Chris Menard you are awesome!
Thank you.
Thank you, this is exactly what I needed!
Glad it was helpful!
Thank you for your great explanation. I think it's easier if we use MAXIFS function right?
What if the key word is not there how do i avoid the result being 00/01/1900?
thank you this is really helpful
Thanks, this saved my day in the office today.
Glad to help.
It was very helpful..thank u
Thanks a ton, it really helps me to find the latest date in 100000 rows of database
Good to know. Thanks, Mohan.
For anyone has problem about their date format, ex : 1/0/1900, press "Shift+Enter" when entering the formula instead just "Enter". It's because we are using the array function
Hi, I have pressed, just enter, shift+enter and ctrl+shift+enter nothing is helping it's giving result as 1/0/1900. Could you please help me
This is great. Thank you so much!
You're very welcome!
Thanks! You get a LIKE from me!
The amount of times this video has saved my next!!
Thank you!
Hi Chris, thank you for this video. This was really helpful... :-)
Glad to help.
Super helpful, thanks!
Thanks, Kiran. Glad it worked.
Thank you so much!!
Thanks, great explanation :)
Glad you enjoyed it!
Thank you Sir. It worked
You're welcome!
Great tip! Thank you!
You are so welcome!
THANK YOU SO MUCH!!!!
subscribed!
Thanks for the sub!
Thanks
Welcome
Exactly what I needed for my manager!
Glad to help.
was a big help THANK YOU
Glad to help.
Beautifull Solution!
Thank you.
Thanks from India 🇮🇳
Glad to help! Thank you.
Thank you so much
You're most welcome
Thank you so much, this is what i am exactly looking for , you save my time
Thank you.
Thank you
Hi this is great but i have a query..
If we wish to find the 2nd highest value for a specific company then how do we calculate?
you're the mannnnnnnnn. thank you.
You're welcome!
This is awesome! thank you very much!!!
You're very welcome!
@@ChrisMenardTraining hi, do you have a video of finding the value with more than and less than. To be specific, to find the value of more than 25% but less than 50%, appreciate if you can direct me to this please, thank you very much!
much needed this, ,thanks for the help... 🤗
You’re welcome 😊
Great explanation thanks a lot :)
welcome.
Hello Chris,
Let say there few columns before the invoice date column, which quantity, item, etc and the end invoice date What is the formula to return the last date.
thank you
Thanku❤❤❤
You're welcome.
Super helpful!
Thank you!
Thank you. Can we find the latest date with filled cells? I'm battling with and need help. Thanks in adance.
thank you so much!
You're welcome
this has helped me, thank you.
You're welcome!
Thanks for the video
You bet
Excellent
Thank you!!!
You're welcome!
thank you!
You're welcome!
Thanks for the trick,But Why are the cells showing zero after closing the file and open it again until re enter control shift enter in each cells? Please advise
Chris how can i obtain a formula for the date of last appointment. love your tutorials
Thank you, can we get output based on the latest date plus time criteria
This is helpful, but could you please explain if I have more than 10L rows, how to do it?
Good Day Sir. I want you to assist me with something, please.
I have a spreadsheet for my products, those products change costs from the manufacturers on a regular basis and if it happened I will have to change my sales price as well, please how can I make it dynamic so that the new price will not override the old sales already recorded?
I want if any of my product prices changed it should only be effective on the current sales, not the formal.
Greaaaat sir thank you but can it possible to find second last date of invoice with formulas
Thanks for the video!
Edit - just figured out my solution - Using MAXIFS
does this work with with AND? I'm having trouble getting it to work
ie MAX(IF(AND(Logic 1, Logic 2),Date Range)
that is a good solution. Thanks, Mike!
This helped and one more query on this , the latest date is there - i need the corresponding rates for the dates returned
Hlo Chris, thanks for this video. It was just what I was looking for. How do I return a different item rather the item. in the If statement? For example if there were ticket prices and you want to return the ticket price rather than the date to another table where the airline is displayed but no ticket price. I want to insert the latest ticket price for the airline. II tried using offset but could not get it to work.
Chris, if you had a list of data like this date person reading
1-May-20 George Kline 560
15-May-20 car radio 1043
13-Jun-19 firm lawyer 890
30-Jun-19 bill debasher 356
1-Jan-21 Denton voles 87
15-Jan-21 Mitch mcdere 900
16-Jan-21 car radio 235
6-May-20 George Kline 764
28-Jun-19 firm lawyer 809
11-Jun-20 bill debasher 445
14-Jan-21 Denton voles 434
24-Jan-21 Mitch mcdere 876
How do I use a formula to output the difference in dates for a specific person with the readings on a separate tab in excel. Such as these columns:
may readings June readings July readings
Any help would be appreciated.
Use a Pivot table and it will do the months for you.
Legend!!! if i said it once I said a million times!
Thank you!
Hi Chris, please help me....how to plug into daily the start and end dates example product Mango start season is May 1 until Aug 31....the Mango season should be reflected on an excel dates from May 1, May 2 until the end date ....pls help thank you so much 😊
Hi Chris - If you had Invoice dates for mulltiple years and you wanted to find the earliest date based on multiple Clients, how would you do that using Aggregate,15,3?
I would make a PivotTable and use the Min for the date. One Screenshot drive.google.com/file/d/1j-2kQa_4TBQl-Li9VW4OupbuvFsIlefw/view?usp=drivesdk
Thanks😍😍😍😍😍😍😍
Sir I want that two different batches should hold the same date for the same month atleast a one date gap among them would be enough. I tried hard but could not get the result. Please help me
Chris
i tried your formula but it gives zero or the date formant 1/0/1900 , Can you please help
What to do if there is product column with different products?
Does this work when referencing an excel query? I keep obtaining a zero value as the answer. Please advise.
U can do first b4 formula
Ctrl+shift+enter
Good Sir
Thank you.
What if date changed to Time (HH:mm)? Can we get earliest and latest time?
I have an excel sheet that shows multiple clients and multiple dates each client had a transaction
I want to find the first date for each client and then how many days passed between each of multiple transactions since the first transaction for the particular client. How do I do this?
you would sort by client A to Z and then by Date. in the next columns add your calculation. This solution works, but if you have a lot of customers it may not be the best solution. drive.google.com/file/d/1kNF3mK7P-a9eM6ZR9D1Ei1SKZhW7qzAF/view?usp=drivesdk
hello , i applied the formula but got an error as result , i think the cell type is wrong , can somene help me please i will be thankful
Hi, I can't get my data to become the min, it only shows up as 00/01/1900. MinIFS doesn't work either
What is the significance of CTRL+SHIFT+ENTER?
On my report, if there are no dates entered in my group of cells, how can I show a blank, or a “n/a”
=if(cell="",n/a,cell)
SIR MAX FORMULA IS WORKING PROPERLY BUT MIN IS NOT.................
Keep getting "0" or default short date "1/0/1900". Any tips on how to overcome this?
Can you send me your file? chrisamenard@gmail.com
I have the same issue using min and max - any advice???
@@ChrisMenardTraining I keep getting the same result, any ideas?
Use MAXIFS
@@ChrisMenardTraining i also facing same problem.
what happens if you don't use the parentheses?
Hi Chris Thank you , question i have pms file i want to know is there any way to excel calculate by own and find 3rd date between start and finish date with formula and gives best match date between 2 start and finish date , it's example that can expand for 1000 tasks
Imagine i have 3 tasks with 5 days duration start is 1/1/22 and finish is 1/12/22 so first task start 1 day and 3rd task start 1/7/22 now I want excel calculate and find best date between in 2 tasks that must be 1/4/22
For 3 task i used sumerized 3 task days then or 2nd task i did finished 3rd task mines sum of 3 task divide by 1.3 and get closet date but it hard for 1000 task to start calculate from last task came up to 2nd task
Now is there anyway excel calculate and find this date, this is can use for 1000 tasks more or less that I need to find it
1000000 tnx if you help me 🌹🌹🌹🌹
Thank you 🌹🌹
how do you do it with 2 criteria?
I also getting "0" or default short date "1/0/1900". Any tips on how to overcome this?
Same da case with me :S
use MAXIFS
@@jobber1984 How would I use MAXIFS for this?
Good he
to be slightly technical, the MAX is a search, not calculating, function.
Helpful but I keep getting "0" or 1/0/1900. Help!!!
Nevermind... figured out it was because the data I recieved, the dates had spaces before them. Jesus christ.
Is this formula still working in 2024? Windows 11
Still works.
How to find last to last transaction?
how to do in google sheet??
I'm not sure if this is possible in Google Sheets. If it were, it would probably be the same functions.
Use MAXIFS! or MINIFS
how to get Reminder date before 3 months of validity date in excel
Are you wanting to know 90 days before the invoice date?
Thanks that's was good one, we can also sort the date and use VLOOKUP/XLOOKUP right?
Didne work..
Doesnt work when u take another workbook
Can someone do this same exercise using Excel Power Query? I have millions of rows.
Found the answer: excelgorilla.com/power-query/using-table-max/
not working, it gives result 0
Thank you Sir.
You have saved my week. Now I can complete week task in an hour.
Don't tell you boss. :-)
@@ChrisMenardTraining 🤣🤣 of course I won’t tell. If I do so then I will get more work. 👍🏻
Thanks