Step 1 - Create 3 fields
StartDate
End Date
Difference
Step 2 - Add the following Rule to the startDate field:
Action: Set a field's value
Field: difference
Value:
number(substring(endate, 9, 2)) + floor((153 * (number(substring(endate, 6, 2)) + 12 * floor((14 - number(substring(endate, 6, 2))) / 12) - 3) + 2) / 5) + (number(substring(endate, 1, 4)) + 4800 - floor((14 - number(substring(endate, 6, 2))) / 12)) * 365 + floor((number(substring(endate, 1, 4)) + 4800 - floor((14 - number(substring(endate, 6, 2))) / 12)) / 4) - floor((number(substring(endate, 1, 4)) + 4800 - floor((14 - number(substring(endate, 6, 2))) / 12)) / 100) + floor((number(substring(endate, 1, 4)) + 4800 - floor((14 - number(substring(endate, 6, 2))) / 12)) / 400) - 32045 - (number(substring(., 9, 2)) + floor((153 * (number(substring(., 6, 2)) + 12 * floor((14 - number(substring(., 6, 2))) / 12) - 3) + 2) / 5) + (number(substring(., 1, 4)) + 4800 - floor((14 - number(substring(., 6, 2))) / 12)) * 365 + floor((number(substring(., 1, 4)) + 4800 - floor((14 - number(substring(., 6, 2))) / 12)) / 4) - floor((number(substring(., 1, 4)) + 4800 - floor((14 - number(substring(., 6, 2))) / 12)) / 100) + floor((number(substring(., 1, 4)) + 4800 - floor((14 - number(substring(., 6, 2))) / 12)) / 400) - 32045) + 1
with the following Conditions on the Rule:
startDate is not blank and
endDate is not blank
Step 3 - Add a second Rule to the startDate field with the following settings:
Action: Set a field's value
Field: difference
Value: 0
with the following Conditions on the Rule:
startDate is blank or
endDate is blank
Step 4 - Add the following Rule to the endDate field:
Action: Set a field's value
Field: difference
Value:
(number(substring(., 9, 2)) + floor((153 * (number(substring(., 6, 2)) + 12 * (floor((14 - number(substring(., 6, 2))) / 12)) - 3) + 2) / 5) + (number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) / 12))) * 365 + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) / 12))) / 4) - floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) / 12))) / 100) + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) / 12))) / 400) - 32045) - (number(substring(startdate, 9, 2)) + floor((153 * (number(substring(startdate, 6, 2)) + 12 * (floor((14 - number(substring(startdate, 6, 2))) / 12)) - 3) + 2) / 5) + (number(substring(startdate, 1, 4)) + 4800 - (floor((14 - number(substring(startdate, 6, 2))) / 12))) * 365 + floor((number(substring(startdate, 1, 4)) + 4800 - (floor((14 - number(substring(startdate, 6, 2))) / 12))) / 4) - floor((number(substring(startdate, 1, 4)) + 4800 - (floor((14 - number(substring(startdate, 6, 2))) / 12))) / 100) + floor((number(substring(startdate, 1, 4)) + 4800 - (floor((14 - number(substring(startdate, 6, 2))) / 12))) / 400) - 32045) + 1
with the following Conditions on the Rule:
startDate is not blank and
endDate is not blank
Step 5- Add a second Rule to the endDate field with the following settings:
Action: Set a field's value
Field: difference
Value: 0
with the following Conditions on the Rule:
startDate is blank or
endDate is blank
Step 6 - Add the following Rule to the difference field:
Action: Set a field's value
Field: .
Value: 0
with the following Condition on the Rule:
difference does not match pattern Custom Pattern: -{0,1}\d+
Thursday, March 31, 2011
Wednesday, March 9, 2011
Code for Birthday list where of this particular month
Step 1 - create columns
name , Actualbirthdate , current year , start of month , end of month, BirthDate
Step 2 - place formula in calculated columns
start of month - calculated field [=DATE([Current Year],MONTH(Birthdate),1)]
end of month - calculated field [=DATE([Current Year],MONTH(Birthdate)+1,1)-1]
BirthDate - calculated field [=TEXT(Birthdate,"dd-mmm")]
Step -3 - now put a filter on webpart
Start of Month
is less than or equal to
[Today]
And
End of Month
is greater than or equal to
[Today]
name , Actualbirthdate , current year , start of month , end of month, BirthDate
Step 2 - place formula in calculated columns
start of month - calculated field [=DATE([Current Year],MONTH(Birthdate),1)]
end of month - calculated field [=DATE([Current Year],MONTH(Birthdate)+1,1)-1]
BirthDate - calculated field [=TEXT(Birthdate,"dd-mmm")]
Step -3 - now put a filter on webpart
Start of Month
is less than or equal to
[Today]
And
End of Month
is greater than or equal to
[Today]
Subscribe to:
Comments (Atom)