Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MacLeanMacLean 

Formula: Date based formula

Im trying to figure out a formula for the following:

 

Does a "date" field = today (Today) or this week (Week) or this month (Month) or this year (Year)

 

Basically if I want a field that determins if the date is either "Today", "Week", "Month" or "Year".

 

Seems like it shoud  be possible, but none of my concoctions seem to work.

 

Thanks, Mac

JonathanBaltzJonathanBaltz

I don't have an answer for the Week situation, but you would want to have a boolean field that checks if DateField = TODAY() or MONTH(DateField) = MONTH(TODAY()) or YEAR(DateField) = YEAR(TODAY()) 

 

If one of those is true, then the boolean field is true.
kcpluspluskcplusplus

So in regards to the weeks check, I put this formula together for fun. I think it could bring back whether a date field compared to TODAY() was within a week. It would never fit in a formula field though, it could be a workflow field update, modified a bit to fit as a validation rule or just here for amusement. Just thought I'd post it.

 

It would require two number fields to correspond as the weekday. I called them day1 and day2, and the custom date field, I call datefield__c in the formula.

IF(
AND(
day1 = day2,
TODAY() = datefield__c
),
"true",
IF(
AND(
day1 = 0,
day2 > 0,
TODAY() - date_field__c < 7
),
"True",
IF(
OR(
AND(
day1 = 1,
day2 > 1,
TODAY() - datefield__c < 6
),
AND(
day1 = 1,
day2 = 0,
datefield__c - TODAY() = 1
)),
"True",
IF(
OR(
AND(
day1 = 2,
day2 > 2,
TODAY() - datefield < 5
),
AND(
day1 = 2,
OR(
day2 = 0,
day2 = 1
),
datefield__c - TODAY() >= 0,
datefield__c - TODAY() < 2
)),
"True",
IF(
OR(
AND(
day1 = 3,
day2 > 3,
TODAY() - datefield < 4
),
AND(
day1 = 3,
OR(
day2 = 0,
day2 = 1,
day2 = 2
),
datefield__c - TODAY() >= 0,
datefield__c - TODAY() < 3
)),
"True",
IF(
OR(
AND(
day1 = 4,
day2 > 4,
TODAY() - datefield < 3
),
AND(
day1 = 4,
OR(
day2 = 0,
day2 = 1,
day2 = 2,
day2 = 3
),
datefield__c - TODAY() >= 0,
datefield__c - TODAY() < 4
)),
"True",
IF(
OR(
AND(
day1 = 5,
day2 > 5,
TODAY() - datefield < 5
),
AND(
day1 = 5,
OR(
day2 = 0,
day2 = 1,
day2 = 2,
day2 = 3,
day2 = 4
),
datefield__c - TODAY() >= 0,
datefield__c - TODAY() < 5
)),
"True",
IF(
OR(
AND(
day1 = 6,
day2 > 6,
TODAY() - datefield < 6
),
AND(
day1 = 6,
OR(
day2 = 0,
day2 = 1,
day2 = 2,
day2 = 3,
day2 = 4,
day2 = 5
),
datefield__c - TODAY() >= 0,
datefield__c - TODAY() < 6
)),
"True",
IF(
AND(
day1 = 7,
day2 >= 0,
day2 < 7
),
datefield__c - TODAY() >= 0,
datefield__c - TODAY() < 7
),
"True",
"False"
))))))))