7 Replies Latest reply on Jul 14, 2014 5:09 AM by hidekimurakami

    Date Format 00.00.0000

    ekloeselvp88

      Title

      Date Format 00.00.0000

      Post

      I would like to have a field validated to have this form 00.00.0000 for a date is that possible?  it helps me when doing some sorts and when keeping some value lists sorted for the dates to be keyed in like that but my problem is that my data entry person keeps keying them in differently...

        • 1. Re: Date Format 00.00.0000
          davidanders

          A Date Field will not accept a non-existant year.

          A Text Field will allow this.

          • 2. Re: Date Format 00.00.0000
            ekloeselvp88

            That is fine for a text field but I need it to only allow the format as stated above....2 numbers.2numbers.four numbers...is that possible?

            • 3. Re: Date Format 00.00.0000
              comment_1

              Your request is not quite clear on several counts:

               

              1. "00.00.0000" is not a date format. It can be either MM.DD.YYYY or DD.MM.YYYY.

               

              2. As long as the dates are valid, they will sort properly.

               

              3. Your data entry person cannot key in invalid dates - they will get an error message if they try. However, they CAN enter a valid date in several forms - for example if the date format being used by the file is MM.DD.YYYY, they can enter "12.01.2010" or "12.1.10" or "12/1" and so on - and all these wil be accepted as a valid date.

               

               

              Instead of adding more validation, you can set the field to auto-enter a calculated value (replacing existing value) =

               

              Date ( Month ( Self ) ; Day ( Self ) ; Year ( Self ) )

               

              This will auto-correct any entry as if it were entered in the date format being used by the file at that time. Alternatively, you can use a similar formula in a script to replace previous entries.

              • 4. Re: Date Format 00.00.0000
                ekloeselvp88

                Comment thank you I will clarify

                 

                1. it should have been MM.DD.YYYY

                3.  It isn't that they are keying in invalid dates but i only want the dates to be formated as above with zero's where need be...so if someone entered 12.1.2010 or 12/1/2010 it would either auto correct it to 12.01.2010 or tell the data entry person that the particular entry is not acceptable. I know you can make it display any way you want but this will not correct the problem I am looking at trying to remedy.

                 

                I.e.  I have to databases.  The first database has a date, a time, a key field, and some information fields as well as one field that is a calculation that combines the date and time fields.

                 

                The second databse links with the keyfield from the first and I created a value list using the key field to pull in information but displaying values from the calculation field so that I can tell what record is what that I am pulling in.

                 

                The problem is that the value list won't sort to my liking unless it is in the above form....hope that makes sense.

                • 5. Re: Date Format 00.00.0000
                  comment_1

                  Then the problem is with your calculation: if the result were a timestamp, it would sort properly. But even with a text result, you could format the date yourself within the calculation, instead of bullying the data entry person.

                  • 6. Re: Date Format 00.00.0000
                    ekloeselvp88

                    "But even with a text result, you could format the date yourself within the calculation, instead of bullying the data entry person." 

                     

                    True True....thanks a ton...all it was was making it a time stamp and it fixed it all I appreciate it greatly.

                    • 7. Re: Date Format 00.00.0000
                      hidekimurakami

                           Set Variable [ $Var1; Value:Year ( Get ( CurrentDate ) ) ]
                           If [ Month ( Get ( CurrentDate ) ) > 9 ]
                           Set Variable [ $Var2; Value:Month ( Get ( CurrentDate ) ) ]
                           Else
                           Set Variable [ $Var2; Value:0 & Month ( Get ( CurrentDate ) ) ]
                           End If
                           If [ Day ( Get ( CurrentDate ) ) > 9 ]
                           Set Variable [ $Var3; Value:Day ( Get ( CurrentDate ) ) ]
                           Else
                           Set Variable [ $Var3; Value:0 & Day ( Get ( CurrentDate ) ) ]
                           End If
                           Set Variable [ $$Var0 ; Value:$Var1 & $Var2 & $Var3 ]

                           by Mumu