Announcement

Collapse
No announcement yet.

Date Time Formula - datetime\month(datetime\today())+2

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Date Time Formula - datetime\month(datetime\today())+2

    Wanted to know that if I have used a formula datetime\month(datetime\today())+2, will it denote January of 2023 in the Month of November 2022?

  • #2
    Nah, this one will return "13", you can always try to print the output in Administration -> Formula Sandbox

    Just put there output\printLine(datetime\month(datetime\today())+ 2) and you will see the output at the bottom

    Comment


    • #3
      I have created a report for the sales forecast for January using this formula on the Close Date in Opportunities. It was working fine till last month and now I need to change the formula. Any alternate solution so that I do not have to edit the formula every month?

      Comment


      • #4
        Have you already use the dateformat field? (or can't you use it?)

        https://docs.espocrm.com/administrat...datetimeformat
        Last edited by espcrm; 11-15-2022, 07:46 AM.

        Comment


        • dreginald
          dreginald commented
          Editing a comment
          I am already using it.
          But when we add +1 or + 2 months, it will not work if it is end of the year. Similarly if we add -1 or -2 months, it will not work in the beginning of the year

        • espcrm
          espcrm commented
          Editing a comment
          Ah that made sense! December coming soon.

          Unfortunately I haven't delve into this issue yet so no solution or thread I'm aware of to give you some insight. You probably have to look through the formula/documents and see if there anything that potential give you a "light bulb" moment.

      • #5
        Hi dreginald,

        This formula will work as expected:
        Code:
        datetime\addMonths(datetime\today(), 2);

        Comment


        • dreginald
          dreginald commented
          Editing a comment
          Tried and its not working

        • Jakub Grufik
          Jakub Grufik commented
          Editing a comment
          this one works perfectly

      • #6
        You can use our extension Custom Formulas. Here you have video which shows how to use our extension: https://www.youtube.com/watch?v=9PMxd2A5DO4
        Here you have specific formula which allow to add days, weeks, months, years. https://docs.dubas.pro/extensions/cu...las/#dubasdate

        Comment


        • dreginald
          dreginald commented
          Editing a comment
          Thats Great. Will try it

      • #7
        dreginald,

        It is very strange that the formula I proposed does not work for you. I just tested it in my test instance, and the date is set correctly.

        Could you show on the screenshots exactly how your formula looks like? As well as the settings of the report itself. This will make it easier to help you with this problem.‚Äč

        Comment


        • #8
          @iazovic

          I tried to create a report in Proposal Stage with Close date after two months and used complex expression using formual greater than (datetime\month(datetime\today())+ 2)

          In October 2022, the report showed the opportunities with Close Date of December 2022.

          But in November, it should show the Opportunities with Close Date of Jan 2023 and above. But it is showing NIL.

          I changed the formula as you suggested and it is still showing the same and I have opportunities closing in Jan in Proposal stage.

          Screenshot attached

          Comment


          • #9
            hi,

            try this :

            PHP Code:
            datetime\addMonths(datetime\today(), 2) % 12
            % - numeric modulo

            you can try too with : complex-expression

            Comment


            • #10
              dreginald hey, as you are referring to the MONTH of the CloseDate in your filter I think you should use this formula instead:

              Code:
              datetime\month(datetime\addMonths(datetime\today(), 2))
              Let us know if this works please.

              Comment

              Working...
              X