Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-09-2020, 06:26 AM
Alaska1 Alaska1 is offline Min Time on Specific Date Windows 7 64bit Min Time on Specific Date Office 2007
Advanced Beginner
Min Time on Specific Date
 
Join Date: Nov 2011
Posts: 96
Alaska1 is on a distinguished road
Default Min Time on Specific Date

In the attached file I need to find the Min Time for each start date (column C).
Attached Files
File Type: xlsx min time on date.xlsx (8.8 KB, 8 views)
Reply With Quote
  #2  
Old 02-09-2020, 07:21 AM
p45cal's Avatar
p45cal p45cal is offline Min Time on Specific Date Windows 10 Min Time on Specific Date Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

In E2, either:
=MINIFS($C$2:$C$14,$B$2:$B$14,B2)
which will put the earliest time against all rows, or:
=IF(MINIFS($C$2:$C$14,$B$2:$B$14,B2)=C2,C2,"")
which will only show a time on rows where the time is the minimum for that day.
Reply With Quote
  #3  
Old 02-09-2020, 06:22 PM
Alaska1 Alaska1 is offline Min Time on Specific Date Windows 7 64bit Min Time on Specific Date Office 2007
Advanced Beginner
Min Time on Specific Date
 
Join Date: Nov 2011
Posts: 96
Alaska1 is on a distinguished road
Default

Thank you. I used the below formula but not returning a value. (see attached document) =IF(MINIFS($C$2:$C$14,$B$2:$B$14,B2)=C2,C2,""). If I wanted to add a 1 in column E for the min time for that date, is it possible. Thank you again for your help.
Attached Files
File Type: xlsx min time on date.xlsx (9.3 KB, 9 views)
Reply With Quote
  #4  
Old 02-10-2020, 12:57 AM
ArviLaanemets ArviLaanemets is offline Min Time on Specific Date Windows 8 Min Time on Specific Date Office 2016
Expert
 
Join Date: May 2017
Posts: 869
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

MINIFS() is available for versions: Excel for Office 365, Excel for the web, Excel 2019. So no way to use it with MS Office 2007!


Attached is an example using dynamic Named Ranges and SUMPRODUCT(). Also I used 2 additional (helper) columns - TableRow and DateRow - which you can hide.


(I started with Defined Table and SUMIFS(), but then realized, those also aren't available for 2007 version!)
Attached Files
File Type: xlsx MinStartTime.xlsx (11.7 KB, 7 views)
Reply With Quote
  #5  
Old 02-10-2020, 03:28 AM
p45cal's Avatar
p45cal p45cal is offline Min Time on Specific Date Windows 10 Min Time on Specific Date Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

For Excel 2007, try in E2, array-entered, this formula, copied down:
=IF(C2=MIN(IF($B$2:$B$14=$B2,$C$2:$C$14)),C2,"")


Array-entering means committing the formula to the sheet using Ctrl+Shift+Enter, not just Enter.
Reply With Quote
  #6  
Old 02-10-2020, 04:43 AM
Alaska1 Alaska1 is offline Min Time on Specific Date Windows 7 64bit Min Time on Specific Date Office 2007
Advanced Beginner
Min Time on Specific Date
 
Join Date: Nov 2011
Posts: 96
Alaska1 is on a distinguished road
Default

Thank you for all you help. I have 2007 at home but have another version which is 365. The formula =IF(C2=MIN(IF($B$2:$B$14=$B2,$C$2:$C$14)),C2,"") in 2007 worked. It put a 0 the min date.

Thank you again.
Reply With Quote
  #7  
Old 02-10-2020, 05:03 AM
p45cal's Avatar
p45cal p45cal is offline Min Time on Specific Date Windows 10 Min Time on Specific Date Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

The zero is correct (midnight), you need to format those cells as Time.
Reply With Quote
  #8  
Old 02-10-2020, 05:23 AM
Alaska1 Alaska1 is offline Min Time on Specific Date Windows 7 64bit Min Time on Specific Date Office 2007
Advanced Beginner
Min Time on Specific Date
 
Join Date: Nov 2011
Posts: 96
Alaska1 is on a distinguished road
Default

Will format time. Thank you for all your help.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
OneNote on Android phone View Tags? Enter Date, Time, Date & Time DrTwinkyEsq OneNote 0 03-27-2017 07:54 PM
Microsoft Project 2016 print specific date range stuck on one date martinsalmon Project 1 10-12-2016 11:36 AM
Min Time on Specific Date date & time of last time a recent file was used pgeorgilas Word 1 10-31-2014 08:59 AM
Greater than today at a specific time cangelis Excel 6 10-01-2014 08:48 AM
Count the common time period (month) between two date period of time Barni Excel 6 08-15-2014 07:52 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:42 PM.


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