on 10-07-2015 8:14 PM
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)
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
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.
User | Count |
---|---|
86 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.