month/day script help

[DBusman 10]

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 )

))))))

[Steve Martino 12]

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.

[DBusman 10]

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.

 

 

 

[DBusman 10]

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
))))))))))))))))

 

[Steve Martino 12]

See next post.

[Steve Martino 12]

Ok here it is with a script.  Someone who can write recursive custom functions could most likely turn the script into a field calculation.

 

Edit:  Changed file to accommodate for 1 or 0 event records.

Edited: 9/23/18 @ 9:41est

EventsSampleSMmod1.fmp12

[Steve Martino 12]

Any luck with the sample file?  Good/Bad/Indifferent?