Date Format

[SilviyaK]

Hi all,

I have a text field that I need to format as a date, but it looks like this:

1-15 March 2023

1-5 April 2024

etc...

How can I format it to a date field so it can also take into account the dates in between the given range? For example for the first one it is all days between 1st of March and 15th of March. 

Thanks!

[maheshkuttappa]

Try this code.

tDate:
Load * Inline [
Date
1-15 March 2023
1-5 April 2024
];

Date:
Load
Date as DateID,
Date(Date#(left(Date,Index(Date,'-',-1)-1)& Mid(Date, Index(Date,' ')),'DD MMMM YYYY'),'DD-MM-YYYY')as StartDate,
Date(Date#(Right(Date, len(Date)-Index(Date,'-',-1)),'DD MMMM YYYY'),'DD-MM-YYYY') as EndDate

Resident tDate;

Drop Table tDate;

ContinuesDate:
Load
DateID,
Date(StartDate + IterNo()-1,'DD-MM-YYYY') as Date

Resident Date While StartDate + IterNo()-1 <= EndDate;

[Sivapriya_d · Accepted solution]

Hi,

Hope this helps.

Temp:
Load
PurgeChar(Date,'- ') as Date_ref,
Date#(SubField(Date,'-',1)&mid(Date,Index(Date,' ')),'DD MMMM YYYY') as StartDate,
Date#(SubField(Date,'-',2),'DD MMMM YYYY') as EndDate;
Load * Inline [
Date
1-15 March 2023
1-5 April 2024
];

DateTable:
Load
Date_ref,
Date(StartDate + IterNo()-1) as Date
Resident Temp While StartDate + IterNo()-1 <= EndDate;

[SilviyaK]

Thanks! This worked perfectly with my data!