6 Replies Latest reply on Jan 22, 2012 9:54 AM by Jonna

    Date to month number?

    Jonna

      Title

      Date to month number?

      Post

      I have 2 fields; a date field (StartDay), and another field with a function Month(StartDay), which is supposed to return a number representing the month. No matter what the date is, the number returned is always "1".

      What is wrong?

      In the Help it reads: "Month(“3/19/2010”) returns 3. This example assumes that the operating system date format is set to MM/DD/YYYY."

      Must I have my operating system date format is set to MM/DD/YYYY? It's now DD/MM/YYYY (European).

       

      Thanks

        • 1. Re: Date to month number?
          raybaudi

          Must I have my operating system date format is set to MM/DD/YYYY?

          No, but you have to be sure that StartDay is a REAL date field ( not a text field )

          • 2. Re: Date to month number?
            Jonna

            I found a VERY strange solution: using the function Month(StartDay) as it should be used, but in the layout I formatted the result (a date field) to show only the first 2 digits of the DAY (!). Now it works.

            Go figure.

            • 3. Re: Date to month number?
              Sorbsbuster

              Formatting has no effect upon calculations.

              As raybaudi asked, is the field StartDay defined as a date field?  Note that the Help example used literal text.  If StartDate is a true date field and is entered in the format consistent with the system settings (ie: dd/mm/yyyy in your case, presumably) then it will correctly return the month number.

              • 4. Re: Date to month number?
                Jonna

                Both fields are proper date fields. The date input in the StartDay field is done with the calendar at end of the field. If the result field is not formatted as mentioned, a date 16.01.2012 will show as 1.1.0001, instead as a month number.

                • 5. Re: Date to month number?
                  Sorbsbuster

                  But the Month calculation should be set to return the result type 'Number'.  What you are showing is that you have set the MonthNumber to be a 'Date' also, so it is displaying the number 1 as the first day after 00/01/0001.

                   

                  (Hence your result can never be more than 12, so will never make its way beyond the middle of January, apparently...)

                  • 6. Re: Date to month number?
                    Jonna

                    Ok, changing it to a number field solved the puzzle for me.

                    Thanks

                     

                    Jonna