Jump to content
DBusman

month/day script help

Recommended Posts

DBusman

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 )

))))))

Share this post


Link to post
Share on other sites
Steve Martino

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.

Share this post


Link to post
Share on other sites
DBusman

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

Share this post


Link to post
Share on other sites
DBusman

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

Share this post


Link to post
Share on other sites
Steve Martino

See next post.

Share this post


Link to post
Share on other sites
Steve Martino

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

Share this post


Link to post
Share on other sites
Steve Martino

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×