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
@@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!
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.
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
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.
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.
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
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)
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?
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.
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
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.
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 😊
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
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
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 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.
you're a hero. this is awesome. saved me hours and hours of work. Thanks
You're welcome!
Thank U for the simplest explanation
Welcome!
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 you Sir.
You have saved my week. Now I can complete week task in an hour.
Happy to help
The amount of times this video has saved my next!!
Thank you!
Thanks a ton, it really helps me to find the latest date in 100000 rows of database
Good to know. Thanks, Mohan.
I was looking for exactly this! Thanks so much Chris.
Glad it was helpful! Thanks, Rachit.
Thank so much for this simple way to share this.. I have subscribed you for more these types of videos to improve my skill.
Very useful formula👍🏻, helped me saving lot of time.
Wow, Chris Menard you are awesome!
Thank you.
Thank you so much, this is what i am exactly looking for , you save my time
Thank you.
Yeee it worked precisely to my working projects. This particular lesson has made my project looked genius 🙏 🤙 😁 👏
Great to hear! Thank you, Dino.
What if the key word is not there how do i avoid the result being 00/01/1900?
Great explanation thanks a lot :)
welcome.
THANK YOU SO MUCH!!!!
subscribed!
Thanks for the sub!
This is way simpler than everything I was trying, thank you for posting@😁
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
much needed this, ,thanks for the help... 🤗
You’re welcome 😊
Exactly what I needed for my manager!
Glad to help.
Greetings frm Malaysia.!!
Thank you so much Chris!, easy to understand and ease my works
Thank you Nur from Malaysia!
Awesome video and great use of formulae and array function!
Thank you!
Hi Chris, thank you for this video. This was really helpful... :-)
Glad to help.
this has helped me, 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!
Thanks, this saved my day in the office today.
Glad to help.
Thank you, this is exactly what I needed!
Glad it was helpful!
was a big help THANK YOU
Glad to help.
Super helpful!
Thank you!
Thanks from India 🇮🇳
Glad to help! Thank you.
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.
Beautifull Solution!
Thank you.
you're the mannnnnnnnn. 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
thank you this is really helpful
Thank you Sir. It worked
You're welcome!
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.
Super helpful, thanks!
Thanks, Kiran. Glad it worked.
Thank you so much
You're most welcome
Thanks, great explanation :)
Glad you enjoyed it!
Thank you, can we get output based on the latest date plus time criteria
thank you!
You're 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.
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
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!
Thanks for the video
You bet
Thank you. Can we find the latest date with filled cells? I'm battling with and need help. Thanks in adance.
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! You get a LIKE from me!
Legend!!! if i said it once I said a million times!
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?
Thank you for your great explanation. I think it's easier if we use MAXIFS function right?
Chris how can i obtain a formula for the date of last appointment. love your tutorials
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
Thanku❤❤❤
You're welcome.
Thanks😍😍😍😍😍😍😍
Greaaaat sir thank you but can it possible to find second last date of invoice with formulas
Thank you!!!
You're welcome!
Thank you
Chris
i tried your formula but it gives zero or the date formant 1/0/1900 , Can you please help
Thanks
Welcome
This helped and one more query on this , the latest date is there - i need the corresponding rates for the dates returned
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.
thank you so much!
You're welcome
It was very helpful..thank u
thank you
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
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.
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.
This is helpful, but could you please explain if I have more than 10L rows, how to do it?
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 😊
Thank you so much!!
Thankyou sir
You are welcome.
What to do if there is product column with different products?
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
Excellent
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
how do you do it with 2 criteria?
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
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.................
what happens if you don't use the parentheses?
What if date changed to Time (HH:mm)? Can we get earliest and latest time?
Good Sir
Thank you.
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?
What is the significance of CTRL+SHIFT+ENTER?
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 🌹🌹
What if I want list of 10 lastest date
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.
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.
How to find last to last transaction?
to be slightly technical, the MAX is a search, not calculating, function.
Is this formula still working in 2024? Windows 11
Still works.
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.
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?
Good he
not working, it gives result 0
Use MAXIFS! or MINIFS
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/