Convert Text to Date Formula

[esforim]

Hi all,

Anyone convert text to date using formula yet? I can't "Light Bulb" on how I can do this.

For example this is one format that many email people would use these format, I need to convert it to numbers so that I can use it for "dueDate"

"31 July 2023"

or "31 Jul 2023".

I try variation of this but fail:

datetime\format("31/07/2023", 'America/New_York', 'DD/MM/YYYY')

Looking at documents, there doesn't seem to be any good way to do this, or I didn't see it.
Maybe I need to do bunch of "string\replace"? For example (fake formula, ignore bad syntax error)
ifThen "Jul = 07; jun=06, etc

[shalmaxb]

as far as I see, there is no function in formula like stringtodate (which exists in php). So I guess, you would have to do that actually with a big string\replace, which I would not recommend (only by feeling).

[lazovic]

Hi esforim,

In general, the Date and Date-Time fields use the following form for filling: 2023-04-13 (Date) or 2023-04-13 21:21:07 (Date-TIme).

So, in this case, one option would be to actually create a formula that would work with breaking text and replacing certain chunks, as you suggested.

[lazovic]

You can try using the following formula script for such a rework, where you can replace the 31 July, 2023 value with some other text attribute with such a date and where the correct date format is written to the $fullDate variable, which the EspoCRM functions in turn can reformat into something else:

$date = '31 July, 2023';

ifThen(
    string\contains($date, ','),
    $date = string\replace($date, ',', '')
    );
    
$dateArray = string\split($date, ' ');    

$day = array\at($dateArray, 0);
$month = array\at($dateArray, 1);
$year = array\at($dateArray, 2);

ifThen(
    string\length($day) == 1,
    $day = string\concatenate('0', $day)
    );

ifThen(
    $month == 'January' || $month == 'Jan' || $month == 'Jan.',
    $month = '01'
    );
    
ifThen(
    $month == 'February' || $month == 'Feb' || $month == 'Feb.',
    $month = '02'
    );
    
ifThen(
    $month == 'March' || $month == 'Mar' || $month == 'Mar.',
    $month = '03'
    );
    
ifThen(
    $month == 'April' || $month == 'Apr' || $month == 'Apr.',
    $month = '04'
    );
    
ifThen(
    $month == 'May' || $month == 'May.',
    $month = '05'
    );
    
ifThen(
    $month == 'June' || $month == 'Jun' || $month == 'Jun.',
    $month = '06'
    );
    
ifThen(
    $month == 'July' || $month == 'Jul' || $month == 'Jul.',
    $month = '07'
    );
    
ifThen(
    $month == 'August' || $month == 'Aug' || $month == 'Aug.',
    $month = '08'
    );
    
ifThen(
    $month == 'September' || $month == 'Sep' || $month == 'Sept' || $month == 'Sep.' || $month == 'Sept.',
    $month = '09'
    );
    
ifThen(
    $month == 'October' || $month == 'Oct' || $month == 'Oct.',
    $month = '10'
    );
    
ifThen(
    $month == 'November' || $month == 'Nov' || $month == 'Nov.',
    $month = '11'
    );
    
ifThen(
    $month == 'December' || $month == 'Dec' || $month == 'Dec.',
    $month = '12'
    );
    
$fullDate = string\concatenate($year, '-', $month, '-', $day);

[esforim]

Love you! Will test this and feedback.