Discussions

 View Only
  • 1.  I need a field that takes the date field and shows only the month. Formula?

    Posted 06-22-2018 19:41
    Every record has a date that is selected by the user. I need to combine all records within a current month to get totals. I dont want to use a summary table, so if I had a formula date field that would convert the date field to just the name of the month i could combine them. Whats the formula for converting a date to a month?


  • 2.  RE: I need a field that takes the date field and shows only the month. Formula?

     
    Posted 06-22-2018 19:51
    The simplest formula would just be 
    Month([userSelectedDate]) 
    This would return a number for the month and you could make your decisions based on that number.

    If you must have the text name of the month, use
    Case(Month ([userSelectedDate]), 1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December"))

    Hope this helps,

    ~Rob


  • 3.  RE: I need a field that takes the date field and shows only the month. Formula?

    Posted 06-22-2018 19:58
    I'm not understanding why you don't just make a table report that Groups by month, but here is a formula for a formula text field type  which will convert a date to YYYYMM format.

    (not tested)

    ToText(Year([my date field]))
    &
    Right("0" & ToText(Month([My Date field])),2)


  • 4.  RE: I need a field that takes the date field and shows only the month. Formula?

    Posted 06-25-2018 06:06
    H there, I am currently using the formula for my month field, which combines the date and month. Just another alternative to the two solutions above.

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

    $Month