+ Start a Discussion
Sugar GozomSugar Gozom 

Displaying the Day of the Week From a Date (1900,1,7)

Hi everyone,

I'm new to coding here, I would just like to know why do we need to subtract this value "Date(1900, 1, 7)" to a certain date to get the day of the week? I don't quite understand what's with this date? Eg "MOD( TODAY() - DATE( 1900, 1, 7 ), 7 )".

Thanks for the help.
Manish BhatiManish Bhati
It is just a date whose day is known as Sunday. So, they use it to find day of the week and other places where day is to be resulted.

The reason is from the Salesforce Documentation
http://resources.docs.salesforce.com/202/10/en-us/sfdc/pdf/formula_date_time_tipsheet.pdf

To find the day of the week from a Date value, use a known Sunday (e.g. January 7, 1900) and subtract it from the date (e.g. TODAY()) to get the difference in days. The MOD() function finds the remainder of this result when divided by 7 to give the numerical value of the day of the week between 0 (Sunday) and 6 (Saturday).

Mark it as answer if it clear your doubts.
Vasani ParthVasani Parth
+1. Let us take couple of examples to illustrate how this works. January 1, 2006 was a Sunday. Let us start with a date in 2006, say Feb 1, 2006. So Date__c is set to “2006-02-01”. Now (Date__c -DATEVALUE(“2006-01-01″ ) returns number of days after Jan 1, 2006. In this example this gets set to 31.

Now the next step is to convert the elapsed days into elapsed weeks. This is done by dividing by 7. In this example we get the number 4.43. Now we take the Floor of this number to get 4. The last step is to take a modulo with 52. This is to get the week number for current year. The MOD returns 4, since the number is less than 52. Now we have the week number, where the first week is 0. To get the week number, where the first week number is 1, we add 1 to get the final week number is 5.

Please mark this as the best answer if this helps
chathura ranasinghe 10chathura ranasinghe 10
Hi Sugar

Here are some links to refer : 

https://developer.salesforce.com/docs/atlas.en-us.formula_date_time_tipsheet.meta/formula_date_time_tipsheet/formula_examples_dates.htm#!
https://trailhead.salesforce.com/en/content/learn/modules/advanced_formulas/date_formulas

Thanks
Chathura