Formula to find out if 05/06/04 is between 05/01/04 and 05/31/04?

hcl-bot · August 30, 2004, 1:26pm

I am trying to find out if a date is between 2 given dates?

hcl-bot · August 30, 2004, 1:57pm

Subject: it’s getting out of control, plz help again.

I am trying to find out the holidays and mark a field with holidays observed: Labor day, Thanksgiving, but i am getting,

Labor Day: Thanksgiving: Thanksgiving, etc.

how can i make this formule easier and only show 1 instace of Thanksgiving, i thought @unique does this.

here is the formula:

@Unique(

@If(@TextToTime(“1/1/04”)>=last_Day& @TextToTime(“1/1/04”) <=return_day;“New Years Day:”;“”)+

@If(@TextToTime(“1/2/04”)>=last_Day& @TextToTime(“1/2/04”) <=return_day;“New Years Day:”;“”)+

@If(@TextToTime(“2/16/04”)>=last_Day& @TextToTime(“2/16/04”) <=return_day;“President’s Day:”;“”)+

@If(@TextToTime(“5/31/04”)>=last_Day& @TextToTime(“5/31/04”) <=return_day;“Memorial Day:”;“”)+

@If(@TextToTime(“7/5/04”)>=last_Day& @TextToTime(“7/5/04”) <=return_day;“Independence Day:”;“”)+

@If(@TextToTime(“9/6/04”)>=last_Day& @TextToTime(“9/6/04”) <=return_day;“Labor Day:”;“”)+

@If(@TextToTime(“11/25/04”)>=last_Day& @TextToTime(“11/25/04”) <=return_day;“Thanksgiving:”;“”)+

@If(@TextToTime(“11/26/04”)>=last_Day& @TextToTime(“11/26/04”) <=return_day;“Thanksgiving:”;“”)+

@If(@TextToTime(“12/24/04”)>=last_Day& @TextToTime(“12/24/04”) <=return_day;“Christmas:”;“”)+

@If(@TextToTime(“12/27/04”)>=last_Day& @TextToTime(“12/27/04”) <=return_day;“Christmas:”;“”)+

@If(@TextToTime(“12/28/04”)>=last_Day& @TextToTime(“12/28/04”) <=return_day;“Christmas:”;“”)+

@If(@TextToTime(“12/29/04”)>=last_Day& @TextToTime(“12/29/04”) <=return_day;“Christmas:”;“”)+

@If(@TextToTime(“12/30/04”)>=last_Day& @TextToTime(“12/30/04”) <=return_day;“Christmas:”;“”)+

@If(@TextToTime(“12/31/04”)>=last_Day& @TextToTime(“12/31/04”) <=return_day;“Christmas”;“”))

hcl-bot · August 30, 2004, 2:47pm

Subject: Got the “:”'s in the wrong place

@Trim(@Unique(@If([1/1/04]>=last_Day& [1/1/04] <=return_day;“New Years Day”;“”):

@If([1/2/04]>=last_Day& [1/2/04] <=return_day;“New Years Day”;“”):

@If([2/16/04]>=last_Day& [2/16/04] <=return_day;“President’s Day”;“”):

@If([5/31/04]>=last_Day& [5/31/04] <=return_day;“Memorial Day”;“”):

@If([7/5/04]>=last_Day& [7/5/04] <=return_day;“Independence Day”;“”):

@If([9/6/04]>=last_Day& [9/6/04] <=return_day;“Labor Day”;“”):

@If([11/25/04]>=last_Day& [11/25/04] <=return_day;“Thanksgiving”;“”):

@If([11/26/04]>=last_Day& [11/26/04] <=return_day;“Thanksgiving”;“”):

@If([12/24/04]>=last_Day& [12/24/04] <=return_day;“Christmas”;“”):

@If([12/27/04]>=last_Day& [12/27/04] <=return_day;“Christmas”;“”):

@If([12/28/04]>=last_Day& [12/28/04] <=return_day;“Christmas”;“”):

@If([12/29/04]>=last_Day& [12/29/04] <=return_day;“Christmas”;“”):

@If([12/30/04]>=last_Day& [12/30/04] <=return_day;“Christmas”;“”):

@If([12/31/04]>=last_Day& [12/31/04] <=return_day;“Christmas”;"]))

Or you could start getting tricky:

daysOut := @Text(@Explode(@TextToTime(@Text(last_Day) + “-” + @Text(return_day))));

HList := @Text([1/1/04] : [1/2/04] : [2/16/04] : [5/31/04] : [7/5/04] : [9/6/04] : [11/25/04] : [11/26/04] : [12/24/04] : [12/27/04] : [12/28/04] : [12/29/04] : [12/30/04] : [12/31/04]);

HNames := “New Years Day” : “New Years Day” : “President’s Day” : “Memorial Day” : “Independence Day” : “Labor Day” : “Thanksgiving” : “Thanksgiving” : “Christmas” : “Christmas” : “Christmas” : “Christmas” : “Christmas” : “Christmas”;

R1 := @Replace(daysOut; HList; HNames);

R2 := @Replace(R1; daysOut; “”);

@Trim(@Unique(R2))

hcl-bot · August 30, 2004, 3:51pm

Subject: thanks for finding the problem, i am trying to get comfortable with 2nd tricky methods

that is pretty neat what you did on the 2nd method.

hcl-bot · August 30, 2004, 4:14pm

Subject: Explanation of Trickier parts:

Simpler:REM “Convert input into DateRange”;

D1 := @TextToTime(@Text(last_Day) + “-” + @Text(return_day));

REM “@Explode DateRange returns a list of all days between start and end of Range then convert to text”;

daysOut := @Text(@Explode(D1));

REM “Holidays converted to text”

HList := @Text([1/1/04] : [1/2/04] : [2/16/04] : [5/31/04] : [7/5/04] : [9/6/04] : [11/25/04] : [11/26/04] : [12/24/04] : [12/27/04] : [12/28/04] : [12/29/04] : [12/30/04] : [12/31/04]);

HNames := “New Years Day” : “New Years Day” : “President’s Day” : “Memorial Day” : “Independence Day” : “Labor Day” : “Thanksgiving” : “Thanksgiving” : “Christmas” : “Christmas” : “Christmas” : “Christmas” : “Christmas” : “Christmas”;

REM “Take the days out text list and replace any that match HList with the corresponding HNames”;

R1 := @Replace(daysOut; HList; HNames);