Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page How do I get the Max date How do I get the Max date
Register FAQ

Reply
 
Thread Tools Display Modes
  #1  
Old 03-14-2022, 12:59 PM
wheddingsjr wheddingsjr is offline How do I get the Max date Windows 7 64bit How do I get the Max date Office 2016
Advanced Beginner
How do I get the Max date 		 
Join Date: Mar 2017
Posts: 76
wheddingsjr is on a distinguished road
Default How do I get the Max date

Hi all

I have data that has items with the same codes but different dates. I have sorted the data by item number and date descending). I want to mark the first item with the most recent date as 1 and all of the other like items be marked as 0 (see attached example). I tried a few different formulas but none of them worked. Any suggestions would be appreciated.

Thanks
Attached Files
File Type: xlsx Book2.xlsx (10.0 KB, 10 views)
Reply With Quote
  #2  
Old 03-14-2022, 07:53 PM
Alansidman's Avatar
Alansidman Alansidman is offline How do I get the Max date Windows 10 How do I get the Max date Office 2019
עַם יִשְׂרָאֵל חַי
  
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 79
Alansidman will become famous soon enoughAlansidman will become famous soon enough
Default
Attached is a solution using Power Query. You can review the steps taken in the file.
Attached Files
File Type: xlsx Book2.xlsx (34.9 KB, 7 views)
Reply With Quote
  #3  
Old 03-14-2022, 11:23 PM
ArviLaanemets ArviLaanemets is offline How do I get the Max date Windows 8 How do I get the Max date Office 2016
Expert
  
Join Date: May 2017
Posts: 873
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default
Into field C2 enter the formula
=((COUNTIFS($A$2:$A$58,$A2,$B$2:$B$58,">" & $B2)+1)=1)*(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=1)[/CODE]
, and copy down.

And sorting is not essential - the formula returns 1 for 1st record with latest date it finds for given item.
Reply With Quote
  #4  
Old 03-15-2022, 04:57 AM
wheddingsjr wheddingsjr is offline How do I get the Max date Windows 7 64bit How do I get the Max date Office 2016
Advanced Beginner
How do I get the Max date 		 
Join Date: Mar 2017
Posts: 76
wheddingsjr is on a distinguished road
Default
Thanks ArviLaanemets, that worked perfecly!!!
Last edited by wheddingsjr; 03-15-2022 at 05:01 AM. Reason: I was incorrect
Reply With Quote
Reply

« Previous Thread | Next Thread »


Similar Threads
Thread Thread Starter Forum Replies Last Post
Use Original Award Date or Mod Award Date For Date-Based Recurring Tasks KBCasey27 Project 1 04-30-2021 12:22 AM
Change font color if date in column F is prior to today's date. kevinbradley57 Excel Programming 1 12-05-2018 07:35 PM
Microsoft Project 2016 print specific date range stuck on one date martinsalmon Project 1 10-12-2016 11:36 AM
How do I get the Max date How can a document have the same date modified/create date, yes have have actual file content? legaleagle Word 15 01-07-2015 06:58 PM
Excel pivot table with a DATE value field for some reason stops at a certain date angie450 Excel Programming 2 08-19-2014 08:50 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:24 PM.

Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft