4 Replies Latest reply: May 15, 2014 9:52 AM by Grant Perkins RSS

    Getting "Day of Week"

    Mike B

      Is there a way, using three seperate fields, "Year" "Month" "Day" that I can also return the "Day of Week" in an exported report?

        • Getting "Day of Week"
          Data Kruncher

          Mike,

           

          Use the CTOD function to convert your individual (character) fields for year, month, and day to a date.

           

          Then use the Weekday function to get a number for the day of the week of that date.

           

          Finally, build a nested IF formula to determine the day name (Monday) from the Weekday integer (2).

           

          Kruncher

          • Getting "Day of Week"
            Grant Perkins

            Originally posted by Data Kruncher:

            Finally, build a nested IF formula to determine the day name (Monday) from the Weekday integer (2).

             

            Kruncher /b[/quote]Or use an internal lookup table which, presumably, would be constant (and so re-usable) for any other reports from your systems.

             

            An external lookup would also work for users of the Pro version of Monarch.

             

            HTH.

             

             

            Grant

            • Getting "Day of Week"
              Data Kruncher

              Grant,

               

              I thought of the lookup table, but wasn't certain that v7 had that feature, whereas a nested IF, for that need and that size of formula, would certainly work.

               

              Having said that, of course, if the lookup table is available, that's most assuredly the way to go. Much more elegant.

               

              Kruncher

              • Getting "Day of Week"
                Grant Perkins

                Originally posted by Data Kruncher:

                Grant,

                 

                I thought of the lookup table, but wasn't certain that v7 had that feature, whereas a nested IF, for that need and that size of formula, would certainly work.

                 

                /b[/quote]Absolutely agree. It's all down to horses for courses and there are a few ways of getting the result required. Whatever feels comfortable is the way to go.

                 

                  :cool: 

                 

                 

                Grant