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
Ken_m3Ken_m3 

Activity Reporting - changing the week from Monday to Sunday

As our salespeople work on weekends we report activities on a Monday to Sunday basis.

I am trying to create a custom field, based on the activity date but I can't seem to access this field in formulas - is this correct?  Basically I wanted to create a custom field "week ending" - something like the following:

CASE( MOD( DATEVALUE(ActivityDate) - DATE(1900, 1, 7), 7),
0, DATEVALUE(ActivityDate),
1, DATEVALUE(ActivityDate) + 6,
2, DATEVALUE(ActivityDate) + 5,
3, DATEVALUE(ActivityDate) + 4,
4, DATEVALUE(ActivityDate) + 3,
5, DATEVALUE(ActivityDate) + 2,
6, DATEVALUE(ActivityDate) + 1,
DATEVALUE(ActivityDate))

so that I could group by 'week ending' in reports.  The formula works fine if I use CreatedDate but sadly we have some paperbased activities that don't get entered into the system until the following week, and using CreatedDate provides inaccurate results.

I'm open to suggestions (short of modifying the report every week) if anyone has a better idea but I really need to be able to show the activities that happened in a given timeframe - from Monday to Sunday

Cheers
Ken
Jeff TalbotJeff Talbot

Unfortunately ActivityDate can not be used in formulas. You could create a custom date field for your purpose, but of course most of us would consider that a really bad workaround.

Not that this helps, but here's the reason Salesforces gives for this limitation:

Why can't I use Due Date / Activity Date in a formula?

When creating a Custom Formula Field or Validation Rule for activities, it is not possible to use "Due Date" in the formula.

The API name for "Due Date" is ActivityDate. 

The syntax error that occurs when attempting to use this field reads:
'Error: You referenced an unsupported field type called "Date/Time" using the following field: activitydate'

This is misleading, as Date/Time fields *can* indeed be used in both Custom Formula Fields and Validation Rules. 

Instead, the error message is referring specifically to the "Due Date" field.  This field has special internal handling - sometimes it is a Date and sometimes it is a Date/Time, which is determined by internal coding.
Ken_m3Ken_m3
well .. that explains why I can't use it in a formula.

what about an S Control?  Could I get someone to create an inline scontrol that populates a custom field based on the due date or do S controls have the same issues as formulas?