Need help with a formula

[EN6924]

Hi All,

Need help to build a formula-

If a file name contains month name in MMM format and Year in YY format, then I want to create a new column which will produce the last day of that month in dd/mm/yyyy format. 

Example-

Filename 1: ALP_Report_23'Dec24

Filename 2: ALP_Report_22'Mar-24

Filename 3: ALP_Report_02Jan25#

Then it would be - 

Month    Year      Expected column

-------------------------------------------------

Dec        24         31/12/2024

Mar        24          31/03/2024

Jan         25          31/01/2025

[binuacs · Accepted solution]

@EN6924 one way of doing this

[nagakavyasri]

@EN6924 

[EN6924]

I see the file names are changing every month -

ALP_Report_22Mar24

ALP_Report_30th Apr 2024

ALP_Report_26th June 2024

ALP_Report_27th Aug24

ALP_Report_31st Octo24

ALP_Report_23'Dec24

ALP_Report_02Jan25#

Will this be possible now to generate the same?

[flying008]

Hi, @EN6924 

FYI.

1- Parse by :

ALP_Report_(\d{2})(?:[[:alpha:]]{2}\s)?'?([[:alpha:]]{3})(?:[\D]*)(\d{2,4})#?$

2- [Date] by :

DateTimeTrim(DateTimeParse('20' + Right([Year], 2) + [Month],'%Y%b'),'lastofmonth')

File	Day	Month	Year	Date
ALP_Report_22Mar24	22	Mar	24	2024-03-31
ALP_Report_30th Apr 2024	30	Apr	2024	2024-04-30
ALP_Report_26th June 2024	26	Jun	2024	2024-06-30
ALP_Report_27th Aug24	27	Aug	24	2024-08-31
ALP_Report_31st Octo24	31	Oct	24	2024-10-31
ALP_Report_23'Dec24	23	Dec	24	2024-12-31
ALP_Report_02Jan25#	2	Jan	25	2025-01-31

******

otherwise you want to get it by one step tool, maybe the formula can help you:

DateTimeTrim(DateTimeParse(REGEX_Replace([File], "(ALP_Report)_(\d{2})(?:[[:alpha:]]{2}\s)?'?([[:alpha:]]{3})(?:[\D]*)(?:\d{2})?(\d{2})#?$", '$3$4'),'%b%y'),'lastofmonth')

[EN6924]

Wow that is great. Let me try now.

There will be 1 modification - Need to exclude Saturday and Sunday.

i.e. Nov 2024 - Last working day was 29/11/2024, Nov ends on 30th (Saturday)

Need help here.

[flying008 · Accepted solution]

Hi, @EN6924 

You can add a formula to get the [LastWorkDay] :

DateTimeAdd([Date], Switch(DateTimeFormat([Date],'%A'), 0, 'Saturday', -1, 'Sunday', -2), 'day')

****

If above help you get your want, please mark it as a solution and give a like for more share.