0

Formatting dates

Jet Reports Historic Posts

February 04, 2013 07:42

None

Follow

Hi All,

I have tried setting the format on the cell both in design mode and after the report is run but the date formula keeps returning the unformatted date ie. 40939.
Is there a way to set the format in the jet formula or only through excel and if excel only, then why does it not keep the format after the report is run?
The formula I am using is =NP("Formula";"EOMONTH([@[Posting Date]];0)") and am trying to return the date so I can use my pivot table to break this into Qtr, Mth, Year and show the dates on my pivot chart as mmm-yy. In design mode, the formula in question is in column "T"

Please help.

Thanks

Thomas

• Sales profit attempt 5.xlsx

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts February 04, 2013 16:51

  Hi Thomas,
  
  Yes this is something that Excel does. With date columns if you are returning a date type from the database, Jet can detect that and reformat those columns after it creates the Excel table as dates. However, in this case the date is the result of an Excel formula, so Jet doesn't know that it needs to reformat the columns. You should be able to do it fairly easily with an Excel TEXT formula like this:
  
  =NP("Formula","TEXT(EOMONTH([@[Posting Date]];0);""YYYY/MM/DD"")")
  Of course you can change the date format string to whatever format you want. Does this help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts February 05, 2013 08:31

  Hi Hughes,
  
  Many thanks for your reply.
  I managed to convert it using the text formula but the problem is still that the Pivot Table does not recognize it as a date and therefore will not allow me to using the grouping function for QTR, YEAR, MONTH etc.
  
  Do you have another way of converting the date perhaps?
  
  Thanks
  
  Thomas

• 0
  
  Jet Reports Historic Posts February 05, 2013 09:31

  Hi Thomas,
  
  A date in Excel is a number. It's the number of days since 1-1-1900 (or somewhere around that time). What I normally do if I want to use dates in my pivot table and group by period types, I leave the date presentation in the NL(Table) as it is.
  
  In the Pivot table you have the option to format the field as a date, and add groups on it. You can group by date, month, quarter, year. There are some good instruction videos about this on YouTube.
  
  Does that help?

• 0
  
  Mark Berger February 28, 2023 16:43

  I am actually trying to just get to the point where I get a number as opposed to text. When I put a date as an option in the jet report, it is populating the information as text and I can't use it when trying to do a formula referencing that cell. How do I do that?