Totext and Dates

I am working with two date time fields.  My current formula is

if
isnull ({Table.Datefield1}) then
{Table.Datefield2}else
 totext ({Table.Datefield1} , 'dd-MMM-yy')

{Table.Datefield1} and {Table.Datefield2} are Date/time fields and I would like them formatted as dd-MMM-yy.


I tired to change {Table.Datefield2} to text as well, but I get an error saying too many arguments.

a couple of ways to approach this but in any case the output of the formula has to always be the same data type

if isnull ({Table.Date1}) then
totext({Table.Date2},'dd-MM-yy') else
totext ({Table.Date1} , 'dd-MMM-yy')

Or make the output always a date field and then use formatting on that


if isnull ({Table.Date1}) then
{Table.Date2} else {Table.Date1}
Right click the resulting formula field on report canvas and select format field option
Use the "Date and Time" tab to customize it to your desired visual output

Option 2 is usually better if you need to still do grouping or sorting on the result as it keeps the data as a datetime field and you can use the sorts and groups that are unique to that data type.

Edited by DBlank - 27 Feb 2017 at 2:55am

I tried both of your formulas witch no luck.  Apparently what I thought was a Date Time is actually a string.....

Table.Date1 = DateTime
Table.Date2 = String

what is the format of the string and is it consistent?

01/24/2016.02:20:16

Yes it is consistent thorough out


Edited by bremen - 27 Feb 2017 at 3:17am

if isnull ({Table.Date1}) then
DATE({Table.Date2}) else {Table.Date1}

Use the format option on the resulting field

Edited by DBlank - 27 Feb 2017 at 3:20am

I Input the formula and I don't get and error when I check for errors or save and close, but when the formula editor window closes I get an error "Bad Date format string"

Edited by bremen - 27 Feb 2017 at 4:08am

Date2 has non datevalues in it.
How do you want to handle those?

You can use an isdate() condition to exclude the bad formatted dates and prevent the error but you still would need to know how you want the errors handled.