Тёмный
No video :(

Calculate the earliest or latest date in Excel based on criteria by Chris Menard 

Chris Menard
Подписаться 75 тыс.
Просмотров 146 тыс.
50% 1

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 - chrismenardtra...
○ Screenshots - Snagit - chrismenardtra...
○ RU-vid keyword search - TubeBuddy - www.tubebuddy....
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!

Опубликовано:

 

28 авг 2024

Поделиться:

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист
Посмотреть позже
Комментарии : 168   
@anne-marieolivier8318
@anne-marieolivier8318 3 года назад
The amount of times this video has saved my next!!
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
Thank you!
@ajaykushwaha4233
@ajaykushwaha4233 3 года назад
Thank you Sir. You have saved my week. Now I can complete week task in an hour.
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
Happy to help
@mohansawant9885
@mohansawant9885 Год назад
Thanks a ton, it really helps me to find the latest date in 100000 rows of database
@ChrisMenardTraining
@ChrisMenardTraining Год назад
Good to know. Thanks, Mohan.
@AidanSkoyles
@AidanSkoyles 3 года назад
you're a hero. this is awesome. saved me hours and hours of work. Thanks
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
You're welcome!
@joaopedroportugal
@joaopedroportugal 2 года назад
Exactly what I needed for my manager!
@ChrisMenardTraining
@ChrisMenardTraining 2 года назад
Glad to help.
@jaswanthpatnaik125
@jaswanthpatnaik125 2 года назад
Thank you Sir. It worked
@ChrisMenardTraining
@ChrisMenardTraining 2 года назад
You're welcome!
@samreenimam8608
@samreenimam8608 Год назад
Thank you so much
@ChrisMenardTraining
@ChrisMenardTraining Год назад
You're most welcome
@sandeep142
@sandeep142 Год назад
Thanks
@ChrisMenardTraining
@ChrisMenardTraining Год назад
Welcome
@dinog.8606
@dinog.8606 3 года назад
Yeee it worked precisely to my working projects. This particular lesson has made my project looked genius 🙏 🤙 😁 👏
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
Great to hear! Thank you, Dino.
@rajasnkl2428
@rajasnkl2428 Год назад
Very useful formula👍🏻, helped me saving lot of time.
@starblack9419
@starblack9419 3 года назад
Thanks from India 🇮🇳
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
Glad to help! Thank you.
@rachitkalani4440
@rachitkalani4440 2 года назад
I was looking for exactly this! Thanks so much Chris.
@ChrisMenardTraining
@ChrisMenardTraining 2 года назад
Glad it was helpful! Thanks, Rachit.
@khushwantdigpal7580
@khushwantdigpal7580 3 года назад
What if the key word is not there how do i avoid the result being 00/01/1900?
@JoelOchoa
@JoelOchoa 2 года назад
thank you!
@ChrisMenardTraining
@ChrisMenardTraining 2 года назад
You're welcome!
@dinabianco2606
@dinabianco2606 5 месяцев назад
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.
@Chirag73
@Chirag73 9 месяцев назад
Thank so much for this simple way to share this.. I have subscribed you for more these types of videos to improve my skill.
@kistnaalper5512
@kistnaalper5512 4 года назад
Wow, Chris Menard you are awesome!
@ChrisMenardTraining
@ChrisMenardTraining 4 года назад
Thank you.
@rfrost8551
@rfrost8551 4 года назад
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?
@zameerzmr1965
@zameerzmr1965 Год назад
Thank you so much, this is what i am exactly looking for , you save my time
@ChrisMenardTraining
@ChrisMenardTraining Год назад
Thank you.
@ramimansi6077
@ramimansi6077 2 года назад
Thank you
@kevinlawrence1419
@kevinlawrence1419 Год назад
This is way simpler than everything I was trying, thank you for posting@😁
@trexarsgo4479
@trexarsgo4479 4 года назад
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
@mangalansp5494
@mangalansp5494 3 года назад
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
@majedhejazi7249
@majedhejazi7249 Год назад
this has helped me, thank you.
@ChrisMenardTraining
@ChrisMenardTraining Год назад
You're welcome!
@senciddimisin
@senciddimisin 4 года назад
Thanks, this saved my day in the office today.
@ChrisMenardTraining
@ChrisMenardTraining 4 года назад
Glad to help.
@user-il1bz7mk9z
@user-il1bz7mk9z Год назад
thank you
@nursyazwani6163
@nursyazwani6163 4 года назад
Greetings frm Malaysia.!! Thank you so much Chris!, easy to understand and ease my works
@ChrisMenardTraining
@ChrisMenardTraining 4 года назад
Thank you Nur from Malaysia!
@aaruharish1754
@aaruharish1754 Год назад
Great explanation thanks a lot :)
@ChrisMenardTraining
@ChrisMenardTraining Год назад
welcome.
@akashlaskaria
@akashlaskaria Год назад
Thanku❤❤❤
@ChrisMenardTraining
@ChrisMenardTraining Год назад
You're welcome.
@ravilifestylevlogs8496
@ravilifestylevlogs8496 2 года назад
much needed this, ,thanks for the help... 🤗
@ChrisMenardTraining
@ChrisMenardTraining 2 года назад
You’re welcome 😊
@robertaago4209
@robertaago4209 4 года назад
thank you this is really helpful
@mischaspidermisch1966
@mischaspidermisch1966 3 года назад
THANK YOU SO MUCH!!!! subscribed!
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
Thanks for the sub!
@kospap622
@kospap622 4 года назад
was a big help THANK YOU
@ChrisMenardTraining
@ChrisMenardTraining 4 года назад
Glad to help.
@srdjanmiladinovic4934
@srdjanmiladinovic4934 3 года назад
Great tip! Thank you!
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
You are so welcome!
@katies3625
@katies3625 3 года назад
Thank you, this is exactly what I needed!
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
Glad it was helpful!
@umurulhaq1030
@umurulhaq1030 3 года назад
Super helpful!
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
Thank you!
@rohithsh007
@rohithsh007 2 года назад
Hi Chris, thank you for this video. This was really helpful... :-)
@ChrisMenardTraining
@ChrisMenardTraining 2 года назад
Glad to help.
@ajjimenez2099
@ajjimenez2099 3 года назад
you're the mannnnnnnnn. thank you.
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
You're welcome!
@mdaje
@mdaje 3 года назад
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.
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
Use a Pivot table and it will do the months for you.
@jobber1984
@jobber1984 4 года назад
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)
@ChrisMenardTraining
@ChrisMenardTraining 4 года назад
that is a good solution. Thanks, Mike!
@sirebumf
@sirebumf 3 года назад
Thanks! You get a LIKE from me!
@LittleWorld6802
@LittleWorld6802 4 года назад
Thanks for the video
@ChrisMenardTraining
@ChrisMenardTraining 4 года назад
You bet
@proactivechain
@proactivechain 4 года назад
Legend!!! if i said it once I said a million times!
@ChrisMenardTraining
@ChrisMenardTraining 4 года назад
Thank you!
@Abdulrahman.Alnahari
@Abdulrahman.Alnahari Год назад
Thank you for your great explanation. I think it's easier if we use MAXIFS function right?
@ursulainzon838
@ursulainzon838 3 года назад
This is awesome! thank you very much!!!
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
You're very welcome!
@ursulainzon838
@ursulainzon838 3 года назад
@@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!
@paulakrisjane
@paulakrisjane 3 года назад
This is great. Thank you so much!
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
You're very welcome!
@Wenkamuy
@Wenkamuy 5 лет назад
Awesome video and great use of formulae and array function!
@ChrisMenardTraining
@ChrisMenardTraining 5 лет назад
Thank you!
@AkaExcel
@AkaExcel 5 лет назад
Beautifull Solution!
@ChrisMenardTraining
@ChrisMenardTraining 5 лет назад
Thank you.
@gentlemen1984
@gentlemen1984 4 года назад
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
@moments9102
@moments9102 Год назад
Thanks😍😍😍😍😍😍😍
@kiranmehra3395
@kiranmehra3395 4 года назад
Super helpful, thanks!
@ChrisMenardTraining
@ChrisMenardTraining 4 года назад
Thanks, Kiran. Glad it worked.
@nereidenene7731
@nereidenene7731 3 года назад
Thank you!!!
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
You're welcome!
@arunasrinivas1129
@arunasrinivas1129 2 года назад
This helped and one more query on this , the latest date is there - i need the corresponding rates for the dates returned
@ylacoua
@ylacoua 4 года назад
Thanks, great explanation :)
@ChrisMenardTraining
@ChrisMenardTraining 4 года назад
Glad you enjoyed it!
@parshad26
@parshad26 4 года назад
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?
@quincyirang2543
@quincyirang2543 2 года назад
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.
@rustysheriffsbadge1077
@rustysheriffsbadge1077 2 года назад
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?
@ChrisMenardTraining
@ChrisMenardTraining 2 года назад
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
@maggiejolitz6156
@maggiejolitz6156 3 года назад
thank you so much!
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
You're welcome
@atulkhiste9345
@atulkhiste9345 Год назад
Thank you, can we get output based on the latest date plus time criteria
@Sheetalmanohar
@Sheetalmanohar 4 года назад
It was very helpful..thank u
@kimyenchu
@kimyenchu Год назад
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.
@ursulainzon838
@ursulainzon838 3 года назад
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 😊
@datalab365
@datalab365 4 года назад
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.
@grainnewhiteside
@grainnewhiteside Год назад
Chris how can i obtain a formula for the date of last appointment. love your tutorials
@nikhilagrawal7885
@nikhilagrawal7885 3 года назад
This is helpful, but could you please explain if I have more than 10L rows, how to do it?
@rahuljadhav7731
@rahuljadhav7731 11 месяцев назад
What to do if there is product column with different products?
@aduverge07
@aduverge07 3 года назад
Excellent
@Kritish07
@Kritish07 3 года назад
Greaaaat sir thank you but can it possible to find second last date of invoice with formulas
@MhetreMudita
@MhetreMudita 3 года назад
Thank you so much!!
@hirendrashriwas8021
@hirendrashriwas8021 Год назад
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
@ajayafp9177
@ajayafp9177 4 года назад
SIR MAX FORMULA IS WORKING PROPERLY BUT MIN IS NOT.................
@andresaafeindouno3880
@andresaafeindouno3880 3 года назад
Thank you. Can we find the latest date with filled cells? I'm battling with and need help. Thanks in adance.
@marksibert305
@marksibert305 8 месяцев назад
to be slightly technical, the MAX is a search, not calculating, function.
@atulshukal4072
@atulshukal4072 4 года назад
Good Sir
@ChrisMenardTraining
@ChrisMenardTraining 4 года назад
Thank you.
@rickeysantos
@rickeysantos Год назад
What is the significance of CTRL+SHIFT+ENTER?
@nikithamini9670
@nikithamini9670 10 месяцев назад
Chris i tried your formula but it gives zero or the date formant 1/0/1900 , Can you please help
@28goldenboy
@28goldenboy 4 года назад
Does this work when referencing an excel query? I keep obtaining a zero value as the answer. Please advise.
@angskeithvlog
@angskeithvlog 3 года назад
U can do first b4 formula Ctrl+shift+enter
@gregsdoodles4547
@gregsdoodles4547 2 года назад
how do you do it with 2 criteria?
@waljeesworld
@waljeesworld 4 года назад
Keep getting "0" or default short date "1/0/1900". Any tips on how to overcome this?
@ChrisMenardTraining
@ChrisMenardTraining 4 года назад
Can you send me your file? chrisamenard@gmail.com
@thekleincrew5782
@thekleincrew5782 4 года назад
I have the same issue using min and max - any advice???
@timdailey9772
@timdailey9772 4 года назад
@@ChrisMenardTraining I keep getting the same result, any ideas?
@jobber1984
@jobber1984 4 года назад
Use MAXIFS
@Masumaz6
@Masumaz6 4 года назад
@@ChrisMenardTraining i also facing same problem.
@kevinjackson8199
@kevinjackson8199 2 года назад
Use MAXIFS! or MINIFS
@manuthareja6090
@manuthareja6090 Месяц назад
Is this formula still working in 2024? Windows 11
@ChrisMenardTraining
@ChrisMenardTraining Месяц назад
Still works.
@stargazerlse
@stargazerlse 3 года назад
what happens if you don't use the parentheses?
@aymenboujardine2008
@aymenboujardine2008 3 года назад
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
@diniaminarti3403
@diniaminarti3403 2 года назад
What if date changed to Time (HH:mm)? Can we get earliest and latest time?
@christinenelson5067
@christinenelson5067 2 года назад
Hi, I can't get my data to become the min, it only shows up as 00/01/1900. MinIFS doesn't work either
@mohammadafsari4686
@mohammadafsari4686 2 года назад
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 🌹🌹
@RohitKumar-cc7zi
@RohitKumar-cc7zi Год назад
Good he
@mittar07
@mittar07 2 года назад
How to find last to last transaction?
@wreckingopossum
@wreckingopossum 4 года назад
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?
@ChrisMenardTraining
@ChrisMenardTraining 4 года назад
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
@ashimariajose6614
@ashimariajose6614 4 года назад
I also getting "0" or default short date "1/0/1900". Any tips on how to overcome this?
@RehanAli-xy1mx
@RehanAli-xy1mx 4 года назад
Same da case with me :S
@jobber1984
@jobber1984 4 года назад
use MAXIFS
@joaopedroportugal
@joaopedroportugal 2 года назад
@@jobber1984 How would I use MAXIFS for this?
@abrahambhima9073
@abrahambhima9073 3 года назад
how to do in google sheet??
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
I'm not sure if this is possible in Google Sheets. If it were, it would probably be the same functions.
@mikesample5581
@mikesample5581 4 года назад
On my report, if there are no dates entered in my group of cells, how can I show a blank, or a “n/a”
@tiktik9036
@tiktik9036 3 года назад
=if(cell="",n/a,cell)
@joaopedroportugal
@joaopedroportugal 2 года назад
Helpful but I keep getting "0" or 1/0/1900. Help!!!
@joaopedroportugal
@joaopedroportugal 2 года назад
Nevermind... figured out it was because the data I recieved, the dates had spaces before them. Jesus christ.
@tweetydx9967
@tweetydx9967 Год назад
Thanks that's was good one, we can also sort the date and use VLOOKUP/XLOOKUP right?
@sonamdhendup5194
@sonamdhendup5194 4 года назад
how to get Reminder date before 3 months of validity date in excel
@ChrisMenardTraining
@ChrisMenardTraining 4 года назад
Are you wanting to know 90 days before the invoice date?
@sumeetpatil4785
@sumeetpatil4785 4 года назад
Didne work..
@sumeetpatil4785
@sumeetpatil4785 4 года назад
Doesnt work when u take another workbook
@benazirinamdar4424
@benazirinamdar4424 Год назад
not working, it gives result 0
@alanjoustra2745
@alanjoustra2745 3 года назад
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/
@ajaykushwaha4233
@ajaykushwaha4233 3 года назад
Thank you Sir. You have saved my week. Now I can complete week task in an hour.
@ChrisMenardTraining
@ChrisMenardTraining 3 года назад
Don't tell you boss. :-)
@ajaykushwaha4233
@ajaykushwaha4233 3 года назад
@@ChrisMenardTraining 🤣🤣 of course I won’t tell. If I do so then I will get more work. 👍🏻
@FunWithKids-dr2of
@FunWithKids-dr2of 9 месяцев назад
Thanks
Далее
Can You Bend This Bar?
01:00
Просмотров 4,3 млн
Ajdarlar...😅 QVZ 2024
00:39
Просмотров 484 тыс.
COUNTIFS function in Excel with dates by Chris Menard
15:25
3 Essential Excel skills for the data analyst
18:02
Просмотров 1,5 млн
Top 10 Essential Excel Formulas for Analysts in 2024
13:39
Lookup all values and find latest date
6:18
Просмотров 43 тыс.
Lookup the Last Matching Value
6:19
Просмотров 35 тыс.
Can You Bend This Bar?
01:00
Просмотров 4,3 млн