2 Replies Latest reply on Nov 25, 2012 5:29 PM by robwoof

    Adding DAYS to a Date

    ajayz

      Hello,

       

      I have a date field called "ISSUED" (the date they completed training), I want to add either 1 month or 30 days (both options will work) to this date to create a calculated field with the new date called "SOTExpiry".

       

      The Training certificate is only valid for 30days from the date of issued - by this time they should have recieved their qualification card.

       

      I understand that date fields are represented in seconds and thefore I need to add 30 days X 86400 (60 seconds/minutes * 60 minutes/hours * 24 hours/day)

       

      I've created a calculated field with the forumal = ISSUED +30* 86400

       

      I've changed the "calculation result is" Date

       

      however in my report this field only shows " ?"

       

      Can someone point me in the right direction or is there another option I should be looking at?

        • 1. Re: Adding DAYS to a Date
          ErikWegweiser

          Hi, ajayz: If ISSUED is a date field, your expiration date calculation should be one of the following date results:

           

           

          Let(

          start_dt = ISSUED ;

          Date( Month( start_dt ) + 1; Day( start_dt ); Year( start_dt)  )

          )

           

          — or —

           

          Let(

          start_dt = ISSUED ;

          Date( Month( start_dt ) ; Day( start_dt ) + 30 ; Year( start_dt)  )

          )

           

          depending on whether you want to add one month or want to add 30 days.

          • 2. Re: Adding DAYS to a Date
            robwoof

            Hi,

             

            Firstly, I think you're thinking of Time and TimeStamp fields which are represented in seconds ( since 12:00AM and 12:00AM on 1 January 0001 respectively). Dates are treated as integers, with day 1 being 1 January 0001. So to add 30 days:

             

            NewDate = ISSUED + 30

             

            is just what you need.

             

            If you want to add a month, you might try something like:

             

            NewDate = Date ( Month ( ISSUED ) + 1 ; Day ( ISSUED ) ; Year ( ISSUED ) )

             

            For this one, however, a note of caution: If Day ( ISSUED ) = 31 and the following month has 30 days, the result will be First of the second month.

             

            E.g. ISSUED = 31 May 2012, so NewDate = Date ( 5 + 1 ; 31 ; 2012 ) = Date ( 6 ; 31 ; 2012 )

            We know that June only has 30 days, so the month and day will "tick over" and the result will be NewDate = 1 July 2012

             

            You can work around this by testing the result in some way. One option might be something like this:

             

            NewDate =

             

            Let ( [ sDate = ISSUED ;

                      sDay = Day ( ISSUED ) ;

                      sMonth = Month ( ISSUED ) ;

                      sYear = Year ( ISSUED ) ;

                      tDate = Date ( sMonth + 1 ; 1 ; sYear ) - 1 ; //the day before the first of the next month i.e. the last day of ISSUED month

                      tDay = Day ( tDate ) ; // the number of days in ISSUED month

             

            ISSUED + tDay  // if you add the number of days in ISSUED month, NewDate will always be the same day in the following month

             

            )

            1 of 1 people found this helpful