Convert Date Time in table formula reference only

[brian6464]

Not sure if this will make sense. I have an excel table with a range defined as qryFCC_Data1. In this table, there is a field called TransDateTime. This field contains hourly readings for different sensor tags, so data for April 18th shows up as 4/18/2018 0:00, 4/18/2018 1:00, 4/18/2018 2:00 and so on.

The table itself contains 8 days of data...so 192 hourly records.

I am trying to write a dynamic formula that will average the trailing 24 hours of data. What I have is shown below.

=AVERAGEIFS((qryFCC_Data1[FCFC401.PV]),(qryFCC_Data1[TransDateTime]),MAX(qryFCC_Data1[TransDateTime]))

So this finds the column/sensor tag FCFC401.PV and then should average the data points where the values in the TransDateTime column meet the criteria of MAX TransDateTime. So if my data points are 4/11/2018 to 4/18/2018, I want to average all the values for 4/18/2018 for tag FCFC401.PV. However, since the TransDateTime has hourly references, the max value it finds is for 4/18/2018 23:00. I have tested the formula on just data only part and it works great.

So the question: Is there a way for me to format the TransDateTime table value to just Date within the actual AverageIFS formula? In other words, without adding another column of data (these are pulled from Teradata DB), can I convert 4/18/2018 0:00, 4/18/2018 1:00, 4/18/2018 2:00 to just 4/18/2018 on the fly within the formula?

I'm having troubles with it...so not sure it is possible. Thanks in advance.

 

[steve the fish]

You can use two criteria

1) greater or equal to INT(MAX(datetime))
2) less than INT(MAX(datetime))+1

 

[brian6464]

Thanks for the reply, I appreciate it. I had previously tried using INT with MAX and could not get it to work.

Let's say I have a table with 3 values as follows:

TransDateTime FCFC401.PV
1/18/2018 0:00 8400
1/18/2018 1:00 8500
1/18/2018 2:00 8600

I want to be able to average the 3 values and return 8500.

If I use =AVERAGEIFS((qryFCC_Data1[FCFC401.PV]),(qryFCC_Data1[TransDateTime]),INT(MAX(qryFCC_Data1[TransDateTime]))), it will return 8400.

If I use =AVERAGEIFS((qryFCC_Data1[FCFC401.PV]),(qryFCC_Data1[TransDateTime]),MAX(qryFCC_Data1[TransDateTime])), it will return 8600.

I tried some other combinations of using INT with MAX, but I apparently cannot get the syntax right...if I try your suggestions above, I just return errors.

 

[steve the fish]

Try this:

=AVERAGEIFS(qryFCC_Data1[FCFC401.PV],qryFCC_Data1[TransDateTime],">="&INT(MAX(qryFCC_Data1[TransDateTime])),qryFCC_Data1[TransDateTime],"<"&INT(MAX(qryFCC_Data1[TransDateTime]))+1)

 

[brian6464]

This worked perfectly...except that is caused some slight collateral damage to another area of my workbook. There are some additional cells which need to same calc, but DO NOT reference a table. So they look like this:

=AVERAGEIFS(Data!$CN$10:$CN$9000,Data!$DA$10:$DA$9000,">="&INT(MAX(Data!$DA$10:$DA$9000)),Data!$DA$10:$DA$9000,"<"&INT(MAX(Data!$DA$10:$DA$9000))+1)

The formulas hit a range that are shared with about 50 charts. I changed some data range formulas to remove errors by changing NA() to "". The blank cells now allow the MAX date formulas to work in all cases, but it messed with the X axis range on the charts.

I have found that if I change the AVERAGEIFS formulas to the following it returns the right value in the KPI table and the keeps the x Axis labels synced for charts. Do you see any reason why the following formula would not work if I am always just looking for the trailing 1 day average?

=AVERAGEIFS(Data!$CN$10:$CN$9000,Data!$DA$10:$DA$9000,">="&TODAY()-1,Data!$DA$10:$DA$9000,"<"&TODAY())

Thanks for all your help. Much appreciated.