#1
|
|||
|
|||
Min Time on Specific Date
In the attached file I need to find the Min Time for each start date (column C). |
#2
|
||||
|
||||
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. |
#3
|
|||
|
|||
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.
|
#4
|
|||
|
|||
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!) |
#5
|
||||
|
||||
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. |
#6
|
|||
|
|||
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. |
#7
|
||||
|
||||
The zero is correct (midnight), you need to format those cells as Time.
|
#8
|
|||
|
|||
Will format time. Thank you for all your help.
|
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 |
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 |