# MINIFS based on time from datetime column

#### jeff4smith

##### New Member
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)

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### AhoyNC

##### Well-known Member
Maybe something like this if your version of 365 has the FILTER function.

Book1
ABCDE
1Date/TimePressureStart DateEnd Date
212/1/2020 11:0012512/2/202012/5/2020
312/1/2020 15:00119
412/2/2020 8:00117Average
512/2/2020 13:00125MorningAfternoon
612/3/2020 9:00113116.5114.75
712/3/2020 14:00107
812/4/2020 10:00112
912/4/2020 14:00119
1012/5/2020 10:00124
1112/5/2020 14:00108
1212/6/2020 10:00124
1312/6/2020 14:00121
Sheet1
Cell Formulas
RangeFormula
D6D6=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),""))
E6E6=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

##### New Member
Works perfectly! Thank you!

#### AhoyNC

##### Well-known Member
You're welcome. Thanks for the feedback.

Replies
10
Views
347
Replies
5
Views
79
Replies
3
Views
111
Replies
23
Views
393
Replies
1
Views
323

1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back