Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using a combination of Date, Subfield and filebasename() to get month and year

My filenames have the format of "abc_def_jan_2009.xlsx", "abc_def_feb_2009.xlsx" etc

by using


Subfield( filebasename( ), '_', 3) as Month,
Subfield( filebasename( ), '_', 4) as Year


I can get the Month and Year as a text string, but how do i do to get them in a workable date format?

2 Replies
Not applicable
Author

No sure if this is the right way of doing it but this worked if anyone needs it


Month(Date#(Subfield( filebasename( ), '_', 3),'MMM')) AS Month,
Year(Date#(Subfield( filebasename( ), '_', 4),'YYYY')) AS Year


Miguel_Angel_Baeyens

Hello,

Take the whole month and year and format it as date

Date(Date#(Field, 'MMM_YYYY')) AS FileDate


The resulting date would be the first possible date (start of month).

Hope that helps