MINIFS based on time from datetime column

jeff4smith

New Member
Joined
Jan 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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)
 

Some videos you may like

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
Joined
Oct 10, 2011
Messages
4,750
Office Version
  1. 365
Platform
  1. Windows
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),""))
 
Solution

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,750
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top