function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Jaya ValJaya Val 

How to write a Formula Field for getting First day (Monday) of the First Week of the Current Year

Can someone help me in writing  a Formula Field  for getting First day (Monday) of the First Week of the Current Year

For Eg: for the current Year 2021, My formula field should return 12/28/2020
Best Answer chosen by Jaya Val
Zachary SingerZachary Singer
Hi Jaya,
Try this formula 
CASE(WEEKDAY(DATE(YEAR(TODAY()), 1, 1)),
1, DATE(YEAR(TODAY()), 1, 1) + 1,
2, DATE(YEAR(TODAY()), 1, 1),
3, DATE(YEAR(TODAY()), 1, 1) - 1,
4, DATE(YEAR(TODAY()), 1, 1) - 2,
5, DATE(YEAR(TODAY()), 1, 1) - 3,
6, DATE(YEAR(TODAY()), 1, 1) - 4,
DATE(YEAR(TODAY()), 1, 1) - 5)

The Weekday() function returns a numeric value for the day of the week from 1 (Sunday) to 7 (Saturday), so the first line gets the weekday for January 1st of the current year. Based on the numeric result, it then adds, subtracts, or does nothing to the date, based on what day it is.
For example January 1, 2021 is a Friday which returns a value of 6. To get back to Monday we need to subtract 4 days, so again we use DATE() to create January 1, 2021 and then subtract 4.