How to calculate the date for last Sunday in March and in October? - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Roy WilliamsRoy Williams 

How to calculate the date for last Sunday in March and in October?

This will become part of a bigger calculation that will work out the time it takes to update a lead. 

We are based UK and we are currently +1 hour. I understand that any time based calculation will not take into account any local daylight saving hours.

I figure if I can calculate when the DST applies, I can plus/minus one hour into my calculation to get a true result.

Thanks in advance!!
Best Answer chosen by Roy Williams
Ahmad HelalAhmad Helal

Formula to find the Last Sunday in March

 
CASE(MOD( DATE(YEAR(TODAY()), 3, 31) - DATE (1900,1,7),7),
0, DATE(YEAR(TODAY()), 3, 31),
1, DATE(YEAR(TODAY()), 3, 31)-1,
2, DATE(YEAR(TODAY()), 3, 31)-2,
3, DATE(YEAR(TODAY()), 3, 31)-3,
4, DATE(YEAR(TODAY()), 3, 31)-4,
5, DATE(YEAR(TODAY()), 3, 31)-5,
DATE(YEAR(TODAY()), 3, 31)-6
)

Formula to find the Last Sunday in October
 
CASE(MOD( DATE(YEAR(TODAY()), 10, 31) - DATE (1900,1,7),7),
0, DATE(YEAR(TODAY()), 10, 31),
1, DATE(YEAR(TODAY()), 10, 31)-1,
2, DATE(YEAR(TODAY()), 10, 31)-2,
3, DATE(YEAR(TODAY()), 10, 31)-3,
4, DATE(YEAR(TODAY()), 10, 31)-4,
5, DATE(YEAR(TODAY()), 10, 31)-5,
DATE(YEAR(TODAY()), 10, 31)-6
)


 

All Answers

John SzurleyJohn Szurley
Look over this solution from Steve Molis.

https://success.salesforce.com/answers?id=90630000000kp8r
 
Ahmad HelalAhmad Helal

Formula to find the Last Sunday in March

 
CASE(MOD( DATE(YEAR(TODAY()), 3, 31) - DATE (1900,1,7),7),
0, DATE(YEAR(TODAY()), 3, 31),
1, DATE(YEAR(TODAY()), 3, 31)-1,
2, DATE(YEAR(TODAY()), 3, 31)-2,
3, DATE(YEAR(TODAY()), 3, 31)-3,
4, DATE(YEAR(TODAY()), 3, 31)-4,
5, DATE(YEAR(TODAY()), 3, 31)-5,
DATE(YEAR(TODAY()), 3, 31)-6
)

Formula to find the Last Sunday in October
 
CASE(MOD( DATE(YEAR(TODAY()), 10, 31) - DATE (1900,1,7),7),
0, DATE(YEAR(TODAY()), 10, 31),
1, DATE(YEAR(TODAY()), 10, 31)-1,
2, DATE(YEAR(TODAY()), 10, 31)-2,
3, DATE(YEAR(TODAY()), 10, 31)-3,
4, DATE(YEAR(TODAY()), 10, 31)-4,
5, DATE(YEAR(TODAY()), 10, 31)-5,
DATE(YEAR(TODAY()), 10, 31)-6
)


 
This was selected as the best answer
John SzurleyJohn Szurley
+1 Ahmad

That is what Steve Molis pointed to as well.
Roy WilliamsRoy Williams
Cheers chaps - +1 Ahmad :)
Evan PonterEvan Ponter
I followed the same solution, but wanted to post a more character-concious version for anyone running into compile limits:

Formula to find the Last Sunday in March
DATE(YEAR(TODAY()),3,31)
-
MOD(DATE(YEAR(TODAY()),3,31) - DATE(1900,1,7),7)

Formula to find the Last Sunday in October
DATE(YEAR(TODAY()),10,31)
-
MOD(DATE(YEAR(TODAY()),10,31) - DATE(1900,1,7),7)

 
Roy WilliamsRoy Williams
I like your thinking Evan!