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

# Date calculation question

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

--

• ###### 1. Re: Date calculation question

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

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

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)