Value of Date Variable Coming in Number Format

[sunitha_chellaiah]

Dear All,

While using below mentioned expression, value is coming in number date. It should come in Date format.

=$(=Max(DOJ))

I want to use this expression in set analysis. So I am testing this in text object. Kindly see below screenshot.

Value should come as 28-06-2021. But here it is showing -1999.

Please help me to correct the expression.

Thanks in advance.

[PrashantSangle]

use date() on top of it.

like

date(max(yourdatefield))

Regards,

[sunitha_chellaiah]

Hi,

I have tried using date() function. But value is coming as shown in below screenshot.

I am using expression as shown below

[PrashantSangle]

are you sure the value stored in DOJ is in date format not in string??

try below in text box

1: Date(max(DOJ))

2: Date(max(Date#(DOJ,'DD-MM-YYYY')))

No need to write "=$"

[sunitha_chellaiah]

I want to pass that expression in set analysis.

So, I have to use "$".

I have tried using Date# , but I am not getting desired output.

Please help me to correct it.

[Kushal_Chawda]

@sunitha_chellaiah  try below in text object. 

date($(=max(Date)))

While testing the expression in text object "$" is not useful, we use it "$" in set analysis because it is used to evaluate the expression inside set analysis.

If above still not working then try checking in text object without "$" . This is also not working then make sure that your DOJ is in proper Date format.

[hic · Accepted solution]

The dollar expansion messes things up here...

Try this, just to see what Max() returns:
   =Max(DOJ)

If it returns a date, you cannot dollar expand it, because
   =$(=Max(DOJ))
will expand to
   =28-06-2021
which is
   -1999

To solve this, you could try quoting it:
='$(=Max(DOJ))'

[PrashantSangle]

after long time @hic  sir I saw your reply. I always learn something new from your responses.

[tresesco]

Hi @hic ,

---

@hic wrote:

The dollar expansion messes things up here...

Try this, just to see what Max() returns:
   =Max(DOJ)

If it returns a date, you cannot dollar expand it, because
   =$(=Max(DOJ))
will expand to
   =28-06-2021
which is
   -1999

To solve this, you could try
='$(=Max(DOJ))'

---

Let me take this opportunity of getting this clarified - Max(Date), returns a number and it could return a date as well. When does max/min return a number and when a date (i.e. formatted)?

In real cases, now I always test the result and then apply additional date() for formatting. However, if I remember correctly I got (long back) cases when date formatted output used to come directly, but not in recent years; is it related to versions? 

Thanks. 

[hic]

It is indeed linked to version. In some versions, the formatting was lost. We treated this as a minor bug, and it should be fixed now.

The functions return duals, and sometimes the formatting can be kept, sometimes not.

• Sum() and Avg() loses the formatting. The result is a new number that doesn't exist in the source data.
• Min() and Max() should pick up the format of the original data.