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

[@ajaykushwaha4233]

Thank you Sir. You have saved my week. Now I can complete week task in an hour.

[@ChrisMenardTraining]

Happy to help

[@mohansawant9885]

Thanks a ton, it really helps me to find the latest date in 100000 rows of database

[@ChrisMenardTraining]

Good to know. Thanks, Mohan.

[@AidanSkoyles]

you're a hero. this is awesome. saved me hours and hours of work. Thanks

[@ChrisMenardTraining]

You're welcome!

[@anne-marieolivier8318]

The amount of times this video has saved my next!!

[@ChrisMenardTraining]

Thank you!

[@rajasnkl2428]

Very useful formula👍🏻, helped me saving lot of time.

[@dinog.8606]

Yeee it worked precisely to my working projects. This particular lesson has made my project looked genius 🙏 🤙 😁 👏

[@ChrisMenardTraining]

Great to hear! Thank you, Dino.

[@govindasamymuralidharan510]

Thank U for the simplest explanation

[@ChrisMenardTraining]

Welcome!

[@dinabianco2606]

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]

Thank so much for this simple way to share this.. I have subscribed you for more these types of videos to improve my skill.

[@rachitkalani4440]

I was looking for exactly this! Thanks so much Chris.

[@ChrisMenardTraining]

Glad it was helpful! Thanks, Rachit.

[@rfrost8551]

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?

[@trexarsgo4479]

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]

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

[@rickja1]

Hi Chris, I need to create a formula in Excel to identify the earlier of two dates (Ex: 3.6.22 -vs- 6.30.25), in this case 6.30.25, and have this date shown in a cell. I need to track the project completion date with a change order for time extension. Thanks

[@starblack9419]

Thanks from India 🇮🇳

[@ChrisMenardTraining]

Glad to help! Thank you.

[@joaopedroportugal]

Exactly what I needed for my manager!

[@ChrisMenardTraining]

Glad to help.

[@kistnaalper5512]

Wow, Chris Menard you are awesome!

[@ChrisMenardTraining]

Thank you.

[@jobber1984]

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]

that is a good solution. Thanks, Mike!

[@zameerzmr1965]

Thank you so much, this is what i am exactly looking for , you save my time

[@ChrisMenardTraining]

Thank you.

[@nursyazwani6163]

Greetings frm Malaysia.!! Thank you so much Chris!, easy to understand and ease my works

[@ChrisMenardTraining]

Thank you Nur from Malaysia!

[@senciddimisin]

Thanks, this saved my day in the office today.

[@ChrisMenardTraining]

Glad to help.

[@kevinlawrence1419]

This is way simpler than everything I was trying, thank you for posting@😁

[@majedhejazi7249]

this has helped me, thank you.

[@ChrisMenardTraining]

You're welcome!

[@aaruharish1754]

Great explanation thanks a lot :)

[@ChrisMenardTraining]

welcome.

[@mischaspidermisch1966]

THANK YOU SO MUCH!!!! subscribed!

[@ChrisMenardTraining]

Thanks for the sub!

[@Wenkamuy]

Awesome video and great use of formulae and array function!

[@ChrisMenardTraining]

Thank you!

[@ravilifestylevlogs8496]

much needed this, ,thanks for the help... 🤗

[@ChrisMenardTraining]

You’re welcome 😊

[@mdaje]

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]

Use a Pivot table and it will do the months for you.

[@rohithsh007]

Hi Chris, thank you for this video. This was really helpful... :-)

[@ChrisMenardTraining]

Glad to help.

[@kospap622]

was a big help THANK YOU

[@ChrisMenardTraining]

Glad to help.

[@Abdulrahman.Alnahari]

Thank you for your great explanation. I think it's easier if we use MAXIFS function right?

[@rustysheriffsbadge1077]

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]

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

[@quincyirang2543]

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.

[@ursulainzon838]

This is awesome! thank you very much!!!

[@ChrisMenardTraining]

You're very welcome!

[@ursulainzon838]

@@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!

[@jaswanthpatnaik125]

Thank you Sir. It worked

[@ChrisMenardTraining]

You're welcome!

[@katies3625]

Thank you, this is exactly what I needed!

[@ChrisMenardTraining]

Glad it was helpful!

[@khushwantdigpal7580]

What if the key word is not there how do i avoid the result being 00/01/1900?

[@arunasrinivas1129]

This helped and one more query on this , the latest date is there - i need the corresponding rates for the dates returned

[@atulkhiste9345]

Thank you, can we get output based on the latest date plus time criteria

[@ajjimenez2099]

you're the mannnnnnnnn. thank you.

[@ChrisMenardTraining]

You're welcome!

[@umurulhaq1030]

Super helpful!

[@ChrisMenardTraining]

Thank you!

[@AkaExcel]

Beautifull Solution!

[@ChrisMenardTraining]

Thank you.

[@proactivechain]

Legend!!! if i said it once I said a million times!

[@ChrisMenardTraining]

Thank you!

[@robertaago4209]

thank you this is really helpful

[@Viewpoint-1984]

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

[@kiranmehra3395]

Super helpful, thanks!

[@ChrisMenardTraining]

Thanks, Kiran. Glad it worked.

[@samreenimam8608]

Thank you so much

[@ChrisMenardTraining]

You're most welcome

[@parshad26]

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?

[@LittleWorld6802]

Thanks for the video

[@ChrisMenardTraining]

You bet

[@grainnewhiteside]

Chris how can i obtain a formula for the date of last appointment. love your tutorials

[@ylacoua]

Thanks, great explanation :)

[@ChrisMenardTraining]

Glad you enjoyed it!

[@sirebumf]

Thanks! You get a LIKE from me!

[@Kritish07]

Greaaaat sir thank you but can it possible to find second last date of invoice with formulas

[@JoelOchoa]

thank you!

[@ChrisMenardTraining]

You're welcome!

[@akashlaskaria]

Thanku❤❤❤

[@ChrisMenardTraining]

You're welcome.

[@ursulainzon838]

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 😊

[@nikhilagrawal7885]

This is helpful, but could you please explain if I have more than 10L rows, how to do it?

[@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.

[@ramimansi6077]

Thank you

[@nereidenene7731]

Thank you!!!

[@ChrisMenardTraining]

You're welcome!

[@sandy17163]

Thanks

[@ChrisMenardTraining]

Welcome

[@andresaafeindouno3880]

Thank you. Can we find the latest date with filled cells? I'm battling with and need help. Thanks in adance.

[@RinsK]

How to find the previous closest date to an item. Eg: I have a list of multiple dates with multiple items (Row A contains multiple dates for item A, B and C. Now I have a date for item A in row G and i want the exact date or the closest previous date from the date list.

[@Shahzeb-khan910]

Thanks😍😍😍😍😍😍😍

[@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

[@maggiejolitz6156]

thank you so much!

[@ChrisMenardTraining]

You're welcome

[@SaifAli-i3k5k]

thank you

[@Sheetalmanohar]

It was very helpful..thank u

[@nikithamini9670]

Chris i tried your formula but it gives zero or the date formant 1/0/1900 , Can you please help

[@MarketingGuru1236]

Thankyou sir

[@ChrisMenardTraining]

You are welcome.

[@28goldenboy]

Does this work when referencing an excel query? I keep obtaining a zero value as the answer. Please advise.

[@angskeithvlog]

U can do first b4 formula Ctrl+shift+enter

[@waljeesworld]

Keep getting "0" or default short date "1/0/1900". Any tips on how to overcome this?

[@ChrisMenardTraining]

Can you send me your file? [e-mail]

[@thekleincrew5782]

I have the same issue using min and max - any advice???

[@timdailey9772]

@@ChrisMenardTraining I keep getting the same result, any ideas?

[@jobber1984]

Use MAXIFS

[@Masumaz6]

@@ChrisMenardTraining i also facing same problem.

[@rahuljadhav7731]

What to do if there is product column with different products?

[@datalab365]

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.

[@MhetreMudita]

Thank you so much!!

[@ashimariajose6614]

I also getting "0" or default short date "1/0/1900". Any tips on how to overcome this?

[@RehanAli-xy1mx]

Same da case with me :S

[@jobber1984]

use MAXIFS

[@joaopedroportugal]

@@jobber1984 How would I use MAXIFS for this?

[@wreckingopossum]

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]

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

[@rickeysantos]

What is the significance of CTRL+SHIFT+ENTER?

[@aduverge07]

Excellent

[@gregsdoodles4547]

how do you do it with 2 criteria?

[@diniaminarti3403]

What if date changed to Time (HH:mm)? Can we get earliest and latest time?

[@stargazerlse]

what happens if you don't use the parentheses?

[@aymenboujardine2008]

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

[@atulshukal4072]

Good Sir

[@ChrisMenardTraining]

Thank you.

[@mohammadafsari4686]

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 🌹🌹

[@ajayafp9177]

SIR MAX FORMULA IS WORKING PROPERLY BUT MIN IS NOT.................

[@marksibert305]

to be slightly technical, the MAX is a search, not calculating, function.

[@karamelkilany1573]

good morning i need to make report with the latest date of more the 4000 items every item has from 5 or more actions i need the latest date to find the latest price of every item thanks

[@mikesample5581]

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]

=if(cell="",n/a,cell)

[@manuthareja6090]

Is this formula still working in 2024? Windows 11

[@ChrisMenardTraining]

Still works.

[@rohangarg102]

What if I want list of 10 lastest date

[@joaopedroportugal]

Helpful but I keep getting "0" or 1/0/1900. Help!!!

[@joaopedroportugal]

Nevermind... figured out it was because the data I recieved, the dates had spaces before them. Jesus christ.

[@mittar07]

How to find last to last transaction?

[@abrahambhima9073]

how to do in google sheet??

[@ChrisMenardTraining]

I'm not sure if this is possible in Google Sheets. If it were, it would probably be the same functions.

[@kevinjackson8199]

Use MAXIFS! or MINIFS

[@sonamdhendup5194]

how to get Reminder date before 3 months of validity date in excel

[@ChrisMenardTraining]

Are you wanting to know 90 days before the invoice date?

[@RohitKumar-cc7zi]

Good he

[@tweetydx9967]

Thanks that's was good one, we can also sort the date and use VLOOKUP/XLOOKUP right?

[@benazirinamdar4424]

not working, it gives result 0

[@sumeetpatil4785]

Didne work..

[@sumeetpatil4785]

Doesnt work when u take another workbook

[@alanjoustra2745]

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/