Calculating Day of the Week
Sep 18, 2005
Have you ever had a need to know the day of the week an opportunity closed? What about excluding Saturdays and Sundays from your reports so that your dashboards only show business days?
Day of week calculations are easy with formula fields, using the technique described below.
This example calculates the day of the week as a number (0 = Saturday, 1 = Sunday, 2 = Monday, 3 = Tuesday, etc.) for Opportunity Close Date.
Formula Return
Type:
Number
Formula:
MOD( {!CloseDate} - DATE(1900, 1, 6), 7)
Remarks:
- This formula works by picking a known Saturday (January 6, 1900 in the example), subtracting it from the provided date, and using MOD to divide the result by 7 and return the remainder. Thanks to Simon W. and Jesse C. for this example.
- Exclude Saturday & Sunday from reports by adding a report selection criteria of "Day of Week greater than 2".
- A text version of this formula could spell out the day of the week:
CASE(MOD( {!CloseDate} - DATE(1900, 1, 6), 7), 0, "Saturday", 1, "Sunday", 2,
"Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6,
"Friday","")
Bookmark
How do you calculate the day of the week from a (date & Time) field ?
and
Can I calculate a delivery period to exclude weekends ie Collect Friday, Delivered Tuesday = 2 Days and again my date
fields would contain Date+Time ie
28/10/05 10:00
01/11/05 11:15
Posted by: Clive Churchward | October 27, 2005 at 08:59 PM
Will this get thrown off on leap years?
Thanks,
Ryan
Posted by: Ryan McGinn | December 15, 2005 at 03:53 PM
can each day of the month be calculated as a number?
Posted by: rachael | August 05, 2009 at 11:43 AM
This formula doesn't properly handle Daylight Savings Time. I noticed that if I build a report based of this type of field, and try to exclude Saturday and Sunday, values that fall between midnight and 1 AM are included anyways, which throws off the report (and subsequently, the pretty chart I'm looking to create).
Posted by: Paul | August 25, 2009 at 02:03 PM