3 Replies Latest reply on Sep 18, 2015 9:32 AM by nancyhorne@centurytel.net

    Date calculation question

    nancyhorne@centurytel.net

      I need help with the following date calculations. Help would be greatly appreciated!

       

      Fields:

      "frequency" which equals months, 6,12,24,48

      training date

      training date due = training date + frequency - one month (or 30 days)

       

      1. If it is January how can I minus one month? I end up with an error.

       

      2. I want the training date due color to change


      For example:

      Frequency = 12

      Training date: 1/15/2014


      Training date due  12/15/2014 to 1/15/2015 - normal color

      Training date due  1/16/2015 to 2/15/2015 - orange

      Training date due  2/16/2015 to 3/15/2015 - red

       

      --

      Thanks in advance!

        • 1. Re: Date calculation question
          erolst

          1. Try

           

          cTrainingDateDue =

           

          Let (

          d = trainingDate ;

          Date ( Month ( d ) + frequency - 1 ; Day ( d ) ; Year ( d ) )

          )


          2. You need three Conditional Formatting calculations along the lines of

           

          cTrainingDateDue >= Date ( 12 ; 15 ; 2014 ) and cTrainingDateDue <= Date ( 1 ; 15 ; 2015 )

          etc.


          • 2. Re: Date calculation question
            user19752

            For question 2,

            training date due is calculated by formula in 1, then according to it example should be a day.

            Training date due is 12/15/2014

             

            I guess you mean compare the date with "current date"?

            Get(CurrentDate) is between Training date due and Training date due + 1month   normal

            • 3. Re: Date calculation question
              nancyhorne@centurytel.net

              Hello User - Thanks for the question:

              Here is what I wanted:


              TrainingDateDue is 1 month <= today  through today; orange (IOW - for the last month including today) it should be orange

              TrainingDateDue is >  today  ; red


              Here is what works:

              Case(

              TrainingDateDue   ≥   (Today - 30 ) and TrainingDateDue  ≤  Today ; TextColor ( TrainingDateDue ; RGB ( 244 ; 140 ; 20) );

              Today > TrainingDateDue; TextColor ( TrainingDateDue ; RGB ( 255 ; 0 ; 0)) ;

              Date Due)