Forum Discussion

F_Reh2007's avatar
F_Reh2007
Copper Contributor
Sep 18, 2024

Displaying the Earliest (Min) and Latest (Max) Dates in the Chart Title

Good Afternoon,

 

I have a Report where one of the Chart's Heading Textbox must display the Earliest (Min) and Latest (Max) Dates pertaining to the Dataset so that one will know which period the chart is dealing with..

 

 

Rather than hardcoding every week the dates in to the Heading Textbox, is it possible to obtain the Earliest (Min) and Latest (Max) Dates from the Dataset and display these automatically ?

 

I would appreciate any tips.

BI & Data Analysis
Charting
developer
Excel
Formulas and Functions
Macros and VBA
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Sep 18, 2024

    F_Reh2007 

    Insert this formula in any cell, e.g. F14

    ="Previous Week - "&
TEXT(MinValue(Datefield),your_dateformat)&" to "&
TEXT(MinValue(Datefield)+6,your_dateformat)

     

     Select the chart title and type =F14.

     

    Very much untested.

     

    • F_Reh2007's avatar
      F_Reh2007
      Copper Contributor
      Sep 19, 2024

      Thanks. MinValue doesn't appear to work, so I used Min function instead which gives an output but doesn't give a valid value, since there is no date in the Column [AppDate] which is '00/01/1900'. :

       

       

       

      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Sep 19, 2024

        F_Reh2007 

        I suppose I wanted to write:

        ="Previous Week - "&
TEXT(MinValue_Datefield,your_dateformat)&" to "&
TEXT(MinValue_Datefield+6,your_dateformat)

        Where MinValue_Datefield could be MIN() or MINIFS() or a cellreference.

         

        And when your datefield does not contain dates I fail so see why you want to put the non-existent dates in the chart title. 

Resources