In Spread Report dates are places as Text in the cell using the functions listed below.  Many times, simply locating dates using Locate, Date is all you need as you simply want to display the report dates (Date Range selected for the report). 

However, many spread functions can refer to date cells to build more powerful reports.  All of the summary Group functions (GAVG, GMAXZ...) can refer to date cells to summarize the data for that date range. 

Example 1.  Show me the Average Flow from the start of the fiscal year thru the end date of the report.  My fiscal year starts June 1st. 

The GAVG function syntax is:

GAVG(VarNum,GroupOffset/StartDate,Grouping/EndDate) - Since there is no built-in grouping for your fiscal year definition, we need to display the dates in cells and refer to them in the formula.  So, how do I display 6/1/2019 in a cell if the current date is Jan 15th 2020? 

• Locate the End Date of the report: =DATE(-1,"Short Date",-1)
• Use  DFORMAT to display the Year, convert to a number using VAL.  Format number to remove comma (,): =VAL(DFORMAT(B2,"yyyy"))
• Use  DFORMAT to display the month: =VAL(DFORMAT(B2,"m"))
• If the month >=6, use the current year, if < 6 use last year: =if(B4>=6,B3,B3-1)
• Use CONCAT to display the date: CONCAT("6/1/",B5)

Example 2: Find the flow on the date of the max BOD:

First Locate the date of the max BOD using Locate>Group Summary:

Use the V  function to get the value for variable 1 (Inf Flow) for the date of the Max BOD:  V(1,B13)

Example 3: Find the average flow since the last rainfall.

We only enter rain when we have rainfall.  If you enter zero on days with no rain you would need to use Locate>Filtered Dates or Locate>SQL Results to place the date of the last rain > 0.

1. Locate the current date using Locate, Dates 
2. Use the DLV to find the last rainfall
3. Use DATEADD to add one day to the last rainfall. 
4. Use GAVG to get average between those dates.

CONCAT - Combine strings to build a date. 

DATE - Places date based on the Report Dates and Offset.

DATEADD - Add a day, month, etc... to a date.

DFORMAT - Formats a date.

DLV - Date of Last Value