3 Replies Latest reply on May 6, 2015 7:39 AM by philmodjunk

    Reformat Existing Date Column



      Reformat Existing Date Column


      I'm having some issues with my reports because the data has not been correctly validated to ensure users enter dates in a consistent format. So the current data is in a mix of blanks (allowed), DD/MM/YYYY, D/M/YYYY, DD/M/YYYY, D/MM/YYYY and also DD.MM.YYYY. What I'd like to do is reformat all existing dates in one hit, rather than open each record and update according.

      Is there a way to, say, run a function on all entries that does something like:

      Right( "0" & Day( cDate); 2 ) & "/"
      Right( "0" & Month( cDate ); 2 ) & "/"

      Year ( cDate ) & 



        • 1. Re: Reformat Existing Date Column

          Were these values entered into a text field or a field of type date?

          If these are valid dates entered into a date field, you can use the data formatting options at the bottom of the inspector's data tab to specify a date format and all dates will then display with the same format even though they were entered differently.

          • 2. Re: Reformat Existing Date Column

            Thanks, but the data is being exported out and causing me issues when trying to format it via an XSLT template. I'd like to be able to tidy up the existing data as part of my next release, so trying to figure out if it can be done. The data has already been input via a text field which has the correct format, but users seem to have been able to input dates in the varying formats without any issue.



            • 3. Re: Reformat Existing Date Column

              And that would be because:

              The data has already been input via a text field

              Dates should be entered into date fields, not text fields to ensure that valid dates are entered and to better be able to work with them as dates once they are entered. This would also make for simpler data export.

              I suggest defining a calculation field with this expression and a "date" result type:

              GetAsDate ( YourTextFieldHere )

              Then export from this date field. The date formats should now be consistent. This also allows you to do much simpler formatting calculations if you need a specific format for your exported data as you now have a date so you can use things like the month, year, monthname, dayname functions to extract different pieces of the date field.

              Such as:

              Let ( d = GetAsDate ( YourTextFieldHere ) ;

                       Dayname ( d ) & ", " & MonthName ( d ) & " " & Day ( d ) & ", " & Year ( d )

              To produce output such as:  Wednesday, May 6, 2015

              Just to give an example of how much simpler this is once you have cast your data as date instead of text.