Forum Discussion

RyanParr's avatar
RyanParr
Qrew Assistant Captain
8 years ago

Display Month and Year from date field

I have a generic date field.  I'd like it to display MONTH YYYY i.e. JULY 2016.

Tweaking the date field i am able to get close (JULY 1 2016) but the date still shows 1.  Is there a formula that can get me there?

11 Replies

  • Here is some code to use

    var date DateWords = [My Date Field];
    var text Month = Case(Month($DateWords),
    1, "January",
    2, "February",
    3, "March",
    4, "April",
    5, "May",
    6, "June",
    7, "July",
    8,"August",
    9,"September",
    10,"October",
    11,"November",
    12,"December");

    $Month & "  " & ToText(Year($DateWords))
  • RyanParr's avatar
    RyanParr
    Qrew Assistant Captain
    That worked great, Mark! I had to adjust the type of field to be a Formula - Text.  I assumed it should be a date formula field but was wrong.  Thanks for helping again!!!
  • RyanParr's avatar
    RyanParr
    Qrew Assistant Captain
    I've got a curveball to add to the mix. Is it possible to adjust this formula to default to n/a when no date is listed in the [My date field]?
  • sujre, no problem

    var date DateWords = [My Date Field];
    var text Month = Case(Month($DateWords),
    1, "January",
    2, "February",
    3, "March",
    4, "April",
    5, "May",
    6, "June",
    7, "July",
    8,"August",
    9,"September",
    10,"October",
    11,"November",
    12,"December");

    var MMMDDYYY = $Month & "  " & ToText(Year($DateWords));

    if(IsNull([My Date Field]), "n/a", $MMMDDYYY)
  • RyanParr's avatar
    RyanParr
    Qrew Assistant Captain
    It doesn't like the last variable "A variable declaration must be followed by a type."
  • right,
    change
    var MMMDDYYY = $Month & "  " & ToText(Year($DateWords));

    to

    var text MMMDDYYY = $Month & "  " & ToText(Year($DateWords));
  • RyanParr's avatar
    RyanParr
    Qrew Assistant Captain
    I need one more if variable.  If the My Date Field occurred on or before 1977, default to "blank".  I've spent an hour tweaking the formula by myself but haven't gotten anywhere.
  • var date DateWords = [My Date Field];
    var text Month = Case(Month($DateWords),
    1, "January",
    2, "February",
    3, "March",
    4, "April",
    5, "May",
    6, "June",
    7, "July",
    8,"August",
    9,"September",
    10,"October",
    11,"November",
    12,"December");

    var MMMDDYYY = $Month & "  " & ToText(Year($DateWords));

    if(
    IsNull([My Date Field]), "n/a",
    Year([My Date Field]) <= 1977, null,
    $MMMDDYYY)
    • ChristinePratt1's avatar
      ChristinePratt1
      Qrew Cadet
      This formula worked perfectly for me. I do have a question... If I'm working a month behind, how can I modify this formula so that instead of the $MMYYYY showing up as the same month as the [My Date Field], it shows up as a month prior to the [My Date  Field]?

      ------------------------------
      Christine Christine
      ------------------------------
      • AustinK's avatar
        AustinK
        Qrew Commander
        I believe the best way would be to adjust the DateWords variable to be 1 month behind to begin with.

        var date DateWords = AdjustMonth([My Date Field],-1);

        That should do it for you. I tested it on one date myself and it did work.