Jump to content
Salesforce and other SMB Solutions are coming soon. ×

month/day script help


DBusman
 Share

Recommended Posts

I'm using a script which looks at six possible event dates and returns a text string. In this text string result there are three dates that transition from one month into another: October 30, 31 & 1 - Tuesday, Wednesday & Thursday. I need it to read: October 30, 31 & November 1 - Tuesday, Wednesday & Thursday.

I could create a cascading set of arguments such as If(MonthName ( event day 2) ≠ MonthName (event day 1); within the current script below but that would be a mess. Would anyone have a cleaner solution? By the way I realize the current script below is not elegant, but it works.

If(event day 2 = "";

MonthName ( event day 1 ) & " " & Day ( event day 1 ) & " - " & DayName ( event day 1 );

 

If(event day 3 = "";

MonthName ( event day 1 ) & " " & Day ( event day 1 ) & " & " & Day ( event day 2 ) & " - " & DayName ( event day 1 ) & " & " & DayName ( event day 2 );

 

If(event day 4 = "";

MonthName ( event day 1 ) & " " & Day ( event day 1 ) & ", " & Day ( event day 2 ) & " & " & Day ( event day 3 ) & " - " & DayName ( event day 1 ) & ", " & DayName ( event day 2 ) & " & " & DayName ( event day 3 );

 

If(event day 5 = "";

MonthName ( event day 1 ) & " " & Day ( event day 1 ) & ", " & Day ( event day 2 ) & ", " & Day ( event day 3 ) & " & " & Day ( event day 4 ) & " - " & DayName ( event day 1 ) & ", " & DayName ( event day 2 ) & ", " & DayName ( event day 3 ) & " & " & DayName ( event day 4 );

 

If(event day 6 = "";

MonthName ( event day 1 ) & " " & Day ( event day 1 ) & ", " & Day ( event day 2 ) & ", " & Day ( event day 3 ) & ", " & Day ( event day 4 ) & " & " & Day ( event day 5 ) & " - " & DayName ( event day 1 ) & ", " & DayName ( event day 2 ) & ", " & DayName ( event day 3 ) & ", " & DayName ( event day 4 ) & " & " & DayName ( event day 5 );

 

If(event day 6 ≠ "";

MonthName ( event day 1 ) & " " & Day ( event day 1 ) & ", " & Day ( event day 2 ) & ", " & Day ( event day 3 ) & ", " & Day ( event day 4 ) & ", " & Day ( event day 5 ) & " & " & Day ( event day 6 ) & " - " & DayName ( event day 1 ) & ", " & DayName ( event day 2 ) & ", " & DayName ( event day 3 ) & ", " & DayName ( event day 4 ) & ", " & DayName ( event day 5 ) & " & " & DayName ( event day 6 )

))))))

Link to comment
Share on other sites

Very confusing. 

So to simplify, if you have 3 consecutive dates that break over a month, you want to return the 3 dates and their respective days of the week?  If so, what do you want to return if they don't?

I don't understand what 'event day (1-6)' represent, and you check to see if each one is empty.

Some screen shots, sample data with desired result, even a sample file would help.

Link to comment
Share on other sites

The script functions correctly when any date combination falls within the same month. Sometimes the dates "roll over" into the next month as in this example. That's the situation I'm needing to modify the script to handle.

 

 

 

Untitled.jpg

Link to comment
Share on other sites

I've managed to create a long cascading script that works for this issue. It's not elegant but it does the job. If you care to review it, the script is below followed by a screen capture of three example results.  (fyi, Event Date 6 is not addressed because it's very rare to have six event days)

Let ([
M1 = MonthName ( event day 1 );
M2 = MonthName ( event day 2 );
M3 = MonthName ( event day 3 );
M4 = MonthName ( event day 4 );
M5 = MonthName ( event day 5 );
M6 = MonthName ( event day 6 );
D1 = Day ( event day 1 );
D2 = Day ( event day 2 );
D3 = Day ( event day 3 );
D4 = Day ( event day 4 );
D5 = Day ( event day 5 );
D6 = Day ( event day 6 );
DN1 = DayName ( event day 1 );
DN2 = DayName ( event day 2 );
DN3 = DayName ( event day 3 );
DN4 = DayName ( event day 4 );
DN5 = DayName ( event day 5 );
DN6 = DayName ( event day 6 )
];

If(D2 = "";
M1 & " " & D1 & " - " & DN1;
       /* ----- break ----- */

If(D3 = "" and M2 = M1;
M1 & " " & D1 & " & " & D2 & " - " & DN1 & " & " & DN2;

If(D3 = "" and M2 ≠ M1;
M1 & " " & D1 & " & " & M2 & " " & D2 & " - " & DN1 & " & " & DN2;
       /* ----- break ----- */

If(D4 = "" and M2 = M1 and M3 = M2;
M1 & " " & D1 & ", " & D2 & " & " & D3 & " - " & DN1 & ", " & DN2 & " & " & DN3;

If(D4 = "" and M2 ≠ M1 and M3 = M2;
M1 & " " & D1 & ", " & M2 & " " & D2 & ", " & D3 & " - " & DN1 & ", " & DN2 & " & " & DN3;

If(D4 = "" and M2 = M1 and M3 ≠ M2;
M1 & " " & D1 & ", " & D2 & " & " & M3 & " " & D3 & " - " & DN1 & ", " & DN2 & " & " & DN3;
       /* ----- break ----- */

If(D5 = "" and M2 = M1 and M3 = M2 and M4 = M3;
M1 & " " & D1 & ", " & D2 & " & " & D3 & " - " & DN1 & ", " & DN2 & " & " & DN3 & " & " & DN4;

If(D5 = "" and M2 ≠ M1 and M3 = M2 and M4 = M3;
M1 & " " & D1 & " & " & M2 & " " & D2 & ", " & D3 & ", " & D4 & " - " & DN1 & ", " & DN2 & ", " & DN3 & " & " & DN4;

If(D5 = "" and M2 = M1 and M3 ≠ M2 and M4 = M3;
M1 & " " & D1 & ", " & D2 & " & " & M3 & " " & D3 & ", " & D4 & " - " & DN1 & ", " & DN2 & ", " & DN3 & " & " & DN4;

If(D5 = "" and M2 = M1 and M3 = M2 and M4 ≠ M3;
M1 & " " & D1 & ", " & D2 & ", " & D3 & " & " & M4 & " " & D4 & " - " & DN1 & ", " & DN2 & ", " & DN3 & " & " & DN4;
       /* ----- break ----- */

If(D6 = "" and M2 = M1 and M3 = M2 and M4 = M3 and M5 = M4;
M1 & " " & D1 & ", " & D2 & ", " & D3 & ", " & D4 & " & " & D5 & " - " & DN1 & ", " & DN2 & ", " & DN3 & ", " & DN4 & " & " & DN5;

If(D6 = "" and M2 ≠ M1 and M3 = M2 and M4 = M3 and M5 = M4;
M1 & " " & D1 & " & " & M2 & " " & D2 & ", " & D3 & ", " & D4 & " & " & D5 & " - " & DN1 & ", " & DN2 & ", " & DN3 & ", " & DN4 & " & " & DN5;

If(D6 = "" and M2 = M1 and M3 ≠ M2 and M4 = M3 and M5 = M4;
M1 & " " & D1 & ", " & D2 & " & " & M3 & " " & D3 & ", " & D4 & ", " & D5 & " - " & DN1 & ", " & DN2 & ", " & DN3 & ", " & DN4 & " & " & DN5;

If(D6 = "" and M2 = M1 and M3 = M2 and M4 ≠ M3 and M5 = M4;
M1 & " " & D1 & ", " & D2 & ", " & D3 & " & " & M4 & " " & D4 & ", " & D5 & " - " & DN1 & ", " & DN2 & ", " & DN3 & ", " & DN4 & " & " & DN5;

If(D6 = "" and M2 = M1 and M3 = M2 and M4 = M3 and M5 ≠ M4;
M1 & " " & D1 & ", " & D2 & ", " & D3 & ", " & D4 & M5 & " " & D5 & " - " & DN1 & ", " & DN2 & ", " & DN3 & ", " & DN4 & " & " & DN5
))))))))))))))))

 

Untitled.jpg

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use