In Excell using Pi DataLink, can Excell Time/Date formulas, such as "=DATE(YEAR(C10),MONTH(C10),1) 1" be used for "Start time" and "End time" in PI formulas?

If possible, what would the Syntax be?

The following does not work:=PIAdvCalcDat('2017'!$C$11,"DATE(YEAR(C10),MONTH(C10),1)","DATE(YEAR(C10),MONTH(C10),1)+1","1d","average","time-weighted",0,1,0,)

Regards,

Henk

Hi Henk, thanks for posting on PI Square!

Yes, you can utilize Excel time formulas in DataLink queries. Looking at what you have listed above, what exactly are you trying to do? Specifically, what is stored in cell C10? It looks like you are trying to get a time-weighted average perhaps for the last day?

If this is the case, you could definitely use Excel formulas such as:

"=TODAY() - 1" : for starttime, the -1 subtracts one day, as Excel assumes units of days. To subtract an hour, it would be "=TODAY() - 1/24"

"=TODAY()" : for your endtime

You can then use cell references to point the PI DataLink query to these start/end times. Again, let me know a specific example or goal and I should be able to better assist in generating the proper formula.

Thanks,

Adam Fink