8 Replies Latest reply on Jan 27, 2014 5:50 PM by user19752

    Datefields Again :)


      Hi there,


      Again a little question about datefields


      I have a datefield where i now use the calenderpicker but i want to have this date in another field (call it EDITDATE) where the date is YYYYMMDD.


      Erolst was so friendly to provide me this code a month ago :


      Let ( dus = "DATEFIELD ; Left ( dus ; 4 ) & Middle ( dus ; 5 ; 2 ) & Right ( dus ; 2 ) )


      Which worked, but when i use a calenderpicker it uses another dateformat i quess


      In the dateformating is used all kinds of stuff but maybe it's late but i keep getting the wrong format, i think it has something to do with the fact that is say 26-12-2014 but also 26-1-2014 so without the zero.at the januari month.


      The dateformat in my country is dd-mm-yyyy and it has to be formatted to yyyymmdd so without the "-"'s


      Much oblidged for reading and perhaps helping out


        • 1. Re: Datefields Again :)

          Personally I would keep the date field with the calendar as a straight date field and set the EDITDATE field as a calc reflecting the date field. I'm a fan of using Let () for these sorts of calcs, such as:


          Let ( [


          date = yourDateField ;

          theYear = Year ( date ) ;

          lengthM = Length ( Month ( date ) ) ;

          lengthD = Length ( Day ( date ) ) ;

          theMonth = If ( lengthM = 2 ; Month ( date ) ; "0" & Month ( date ) ) ;

          theDay = If ( lengthD = 2 ; Day ( date ) ; "0" & Day ( date ) ) ] ;


          theYear & theMonth & theDay )

          1 of 1 people found this helpful
          • 2. Re: Datefields Again :)

            Why don't you use the date formatting options to display the date in that fashion?




            • 3. Re: Datefields Again :)

              I agree with Keywords.

              In addition, it would be helpful for us (and for you) if you were much clearer about identifying several things:


              Is your field DateFIeld actually defined as a date field?


              Undertand that you can use the layout tools to make the APPEARANCE of this field to look a certain way

              (YYY-MM-DD or DD-YYYY-MM or dayName, YYY-MM-DD) etc


              Note that none of these layout-appearance tools have anything to do with the actual data content of the field.

              In particular trying to calculate Left(myDateField; 4) will not be different for any of the display formats shown above.


              If you're going to to calculations on a date field they need to use the date function such as year( myDateField) or dayName( myDateField); etc.


              The same descriptive detail needs to be included when you talk about EDITDATE.

              Is this a text field? Number field? It cannot be a date field, given your description of how you are using it.

              • 4. Re: Datefields Again :)

                Good points all, Bruce. I should have said that in the calc I posted the calc result should be set to text.

                • 5. Re: Datefields Again :)

                  Hope it is clear that my comments were intended for Bart and his explanations of what he is trying to do and where all the pieces fit and which piece he may be talking about at any particular point.

                  • 6. Re: Datefields Again :)

                    I think if the date>999/12/31, using number field makes things a bit simpler.

                    Let (

                      date = yourDateField ;

                      Year ( date ) * 10000 + Month ( date ) *100 + Day ( date )



                    This result can be text.


                    Usually the date formatting options malcolm said may be the simplest.

                    A case need the calc field is to make a relation with another field that already formatted.

                    • 7. Re: Datefields Again :)

                      Hi everyone,


                      Thank you for the information and explanation.

                      @Bruce, you're right i should have explaned it somewhat more what i was trying to. The field where the data via calendarpicker was used is of course a datefield, the EDITDATE is a textfield which is only used for export to a third party who demand this format. For calculations concerning the dates in other parts of the database i use the datepicker field

                      @Keywords Thank you again for your reply this also works great.

                      @User 19752 This was for me the most clean solution thank you


                      Much Much Oblidged



                      • 8. Re: Datefields Again :)

                        If the field is only used for export, then set the option

                        Do not store calculation results

                        is useful.


                        And another way, there is an option when exporting

                        Apply current layout’s data formatting