Need help with a formula

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

Workflow

Tips and Tricks

Help

Accepted answers

binu_acs

@EN6924 one way of doing this

Date_07012025.yxmd

flying008

Hi, @EN6924

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

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

录制_2025_01_09_09_02_49_776.gif

****

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

All comments

binu_acs

@EN6924 one way of doing this

Date_07012025.yxmd

nagakavyasri

@EN6924

Screenshot 2025-01-07 133922.png

01-07-25.2.yxmd

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')

录制_2025_01_08_15_31_51_619.gif

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

Hi, @EN6924

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

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

录制_2025_01_09_09_02_49_776.gif

****

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703