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,

[DataNath]

Hey @ZahinOsman, I'm just a little bit confused about the process here - am I right in thinking this is a workflow that you have scheduled/run everyday? And therefore all of the 'Daily' records/schedules will be 'Yes' every day, but for those 'Monthly (Monthend)' records, when the workflow runs you want to check each day whether it's the final business day of the month and therefore [Run Today?] should be switched to 'Yes'?

[ZahinOsman]

yes sir

[DataNath]

@ZahinOsman thanks for confirming!

Here's what I've put together. It's worth noting that this only handles weekends - I think if you want to integrate other non-working days like bank holidays etc then you'll need to add another lookup table into this.

1) We generate the current month from first to last day:

2) We Filter out all days that are a Saturday/Sunday:

3) We then find the Maximum (last) day that remains:

4) After appending this to our main table, we then do the following check - if [Schedule] is 'Daily' then we leave it as 'Yes' and if not we check whether the [LastBD] date = today. If so, we change it to 'Yes' and if not we leave it as 'No'.

Finally we just use a Select to remove the temporary [LastBD] date.

Hope this helps!

[Christina_H]

Here's a formula that'll find the last working day of the current month

DateTimeAdd(DateTimeTrim(DateTimeToday(),"lastofmonth"),IIF(DateTimeFormat(DateTimeTrim(DateTimeToday(),"lastofmonth"),"%u")>'5', 5-ToNumber(DateTimeFormat(DateTimeTrim(DateTimeToday(),"lastofmonth"),'%u')),0),"day")

[ZahinOsman]

I owe you my life <3

[ZahinOsman]

Thanks boss

[ShankerV]

Hi @ZahinOsman 

One more way to do with the help of formula tool.

If the same workflow has been ran on 28-04-2023.

Many thanks

Shanker V

[RobertOdera]

Hi, @ZahinOsman 

Try the below.

I hope you find it helpful - Cheers!

[ZahinOsman]

Hey,

for your first part of the formula "DateTimeToday()", do you know how to change this so that it reads T-1?

eg. todays date is 4/3/2023, i would like it to read 3/31/2023. Thus, the weekends are not included.

if it was 4/4/2023, I would like it to read 4/3/2023 and etc.

So it goes by a T-1 basis excluding weekends