Identify last day of the month (Business day)

[ZahinOsman]

Hi All,

I need to write a code to identify the last day of the month, but only on business days. Meaning if the last day of the month is 31st and on a Saturday, I will need it to identify 30th (Friday) as the last day of the month.

To provide context, I have this table below;

Schedule	Run Today?
Daily	Yes
Daily	Yes
Daily	Yes
Daily	Yes
Monthly (Monthend)	No
Monthly (Monthend)	No

If the schedule is Daily, then "Run Today?" will be "Yes".

For "Monthly (Monthend)", I only want it to be "Yes" under "Run Today?" if the date is on the last business day of the month.

if [Schedule] = "Daily" then "Yes"
Else "No"
ENDIF

I currently have this filter written up. Is there anyway I can expand on this to include Monthly (Monthend)?

Best regards,

[ShankerV]

Hi @ZahinOsman 

To read the T-1 day

the below formula helps, 

DateTimeAdd(DateTimeToday(),-1,"days")

For example, if T-1 is Sunday then the below helps to read the Friday date as T-1.

IF [T1]="Sun"
THEN DateTimeAdd([Date],-2,"days")
ELSE [Date]
ENDIF

Note: You can combine both Formula tool as 1 Formula tool. I have made it separate for clear understanding how the formula works and result looks like after each output..

Many thanks

Shanker V

[RobertOdera]

Hi, @ZahinOsman 

Sure thing.

From your note, I now understand that you now also want to qualify your "daily" to only run on weekdays, correct?

Before we were only treating for the end-month scenarios, correct?

Please consider the modifications below:

1. add a field called Daily_Run using this calculation

IF DateTimeFormat(DateTimeToday(),'%a') ='Sat' THEN 'No'

ELSEIF DateTimeFormat(DateTimeToday(),'%a') ='Sun' THEN 'No'

ELSE 'Yes'

ENDIF

2. update the Run_Today logic to this

IF [End_Month_Date] =[Effective_Run_Date] THEN 'Yes' 

ELSEIF !IsEmpty([Daily_Run]) THEN [Daily_Run]

ELSE 'No'

ENDIF

@ZahinOsman it is probably best if you generate a working sample that we can use. I've made some blind assumptions, and we're approaching adverse circular logic.

For example, I would rather proffer filtration logic so that we are reducing your data set as we process it (for efficiency).

Kindly share the current state and desired state samples so that the community can impact you precisely😎 (for effectiveness).

Cheers!