Daylight Savings formula syntax

[jenszulinski]

Hi, I'm working on a formula that will add or subtract minutes based on the daylight savings time.  The formula is defined correctly.

However, when I add it to the report I get an "Error"

=If ((CurrentDate()>=ToDate("03-08";"MM-DD"))And (CurrentDate()<=ToDate("11-01";"MM-DD"));[Age of Incident (minutes)]+240;[Age of Incident (minutes)]+300)

[amitrathi239]

Hi,

use this.

=If ((CurrentDate()>=ToDate("03-08";"MM-dd"))And (CurrentDate()<=ToDate("11-01";"MM-dd"));[Age of Incident (minutes)]+240;[Age of Incident (minutes)]+300)

Amit

[jenszulinski]

Hi Amit,

Happy Friday!

Your formula works, thank you.  However, I have a question.  Based on this logic and today's date I would expect to get Age + 240 since this is the true condition.  However, I am getting Age + 300 instead.  Do I need to change my logic?

=If ((CurrentDate()>=ToDate("03-08";"MM-dd"))And (CurrentDate()<=ToDate("11-01";"MM-dd"));[Age of Incident (minutes)]+240;[Age of Incident (minutes)]+300)

[amitrathi239]

HI

=If ((CurrentDate()>=ToDate("03-08";"MM-dd"))And (CurrentDate()<=ToDate("11-01";"MM-dd"));[Age of Incident (minutes)]+300;Age of Incident (minutes)]+240)

i don't know the requirement  try with this.

Amit

[jenszulinski]

Hi Amit,

After breaking out each formula element realized that the CurrentDate needed to be formatted the same way as DST.

This is what I came up with.  I broke it out into 4 formulas for ease of troubleshooting:

Format current date:

=FormatDate(CurrentDate();"MM-dd")

format start of DST:

=FormatDate(ToDate("03-08";"MM-dd"); "MM-dd")

End of DST:

=FormatDate(ToDate("11-01";"MM-dd"); "MM-dd")

Formula:

= If (([1 - CurrentDate]>=[2 - Start of DST] And [1 - CurrentDate]<=[3 - End of DST]);1;2)

It appears to be working this way.

Thank you so much for your help.  I really appreciate.