MINIFS based on time from datetime column

[jeff4smith]

I'm trying to determine the minimum, maximum, and average blood pressure readings from data exported from a blood pressure machine. The data includes a datetime value, and I would like to calculate different average, max and min values for morning and evening readings.

I would like to use a criteria in the MINIFS formula to determine if the time from a column of datetime values is before noon. I know I could create another column with only the time, but I'm using an imported data set which I do not want to manipulate as it will be refreshed, so I am hoping to have excel evaluate whether the time is before noon from the datetime value only.

I was able to create a similar formula based on the date below to grab the last 30 days, but I cannot figure out how to evaluate based on the time only and would like to add a second condition to this formula to only include values before noon.

=MINIFS(BloodPressure_data[Systolic],BloodPressure_data[Date Time],">="&TODAY()-30)

 

[AhoyNC]

Maybe something like this if your version of 365 has the FILTER function.

Book1
	A	B	C	D	E
1	Date/Time	Pressure		Start Date	End Date
2	12/1/2020 11:00	125		12/2/2020	12/5/2020
3	12/1/2020 15:00	119
4	12/2/2020 8:00	117		Average
5	12/2/2020 13:00	125		Morning	Afternoon
6	12/3/2020 9:00	113		116.5	114.75
7	12/3/2020 14:00	107
8	12/4/2020 10:00	112
9	12/4/2020 14:00	119
10	12/5/2020 10:00	124
11	12/5/2020 14:00	108
12	12/6/2020 10:00	124
13	12/6/2020 14:00	121
Sheet1

Cell Formulas
Range	Formula
D6	D6	=AVERAGE(FILTER($B$2:$B$13,(INT($A$2:$A$13)>=$D$2)*(INT($A$2:$A$13)<=$E$2)*(($A$2:$A$13-INT($A$2:$A$13))>=0)*(($A$2:$A$13-INT($A$2:$A$13))<=0.5),""))
E6	E6	=AVERAGE(FILTER($B$2:$B$13,(INT($A$2:$A$13)>=$D$2)*(INT($A$2:$A$13)<=$E$2)*(($A$2:$A$13-INT($A$2:$A$13))>=0.5)*(($A$2:$A$13-INT($A$2:$A$13))<=1),""))

 

[jeff4smith]

Works perfectly! Thank you!

 

[AhoyNC]

You're welcome. Thanks for the feedback.