How to change a Text columns date format?

Options
Jozef_783863
Jozef_783863 Posts: 331 admin
edited February 2020 in Questions

Hello Catalytic Community! I am in search for a row formula code snippet to change a Text column's date format. The action that needs the row formula is Tables: Apply formulas to columns. Example table image shown, along with expected date format printed below. Any input is appreciated!

Current Text Format:
Month/Day/Year
1/20/20

Desired Text Format:
Year-Month-Day
2020-02-11

Tagged:

Answers

  • Jozef_783863
    Jozef_783863 Posts: 331 admin
    edited February 2020
    Options
     
    var originalDate = columns['Start Date'];
var parts = originalDate.split('/');
parts[0] = parts[0] < 10 ? '0' + parts[0] : parts[0];
columns['Start Date'] = '20' + parts[2] + '-' + parts[0] + '-' + parts[1];

var originalDate2 = columns['End Date'];
var parts2 = originalDate2.split('/');
parts2[0] = parts2[0] < 10 ? '0' + parts2[0] : parts2[0];
columns['End Date'] = '20' + parts2[2] + '-' + parts2[0] + '-' + parts2[1];

  • Kevin_102667
    Kevin_102667 Posts: 56 admin
    edited February 2020
    Options

    @Jozef_783863 I think this will do what you need. This will convert a column with values in any date format to Year-Month-Day (YYYY-MM-DD)
    Also, here's a great guide for working with dates in javascript https://flaviocopes.com/javascript-dates/

    //replace columns[1] with your desired column reference
input = columns[1];
var inputDate = new Date(input)
function formatDate(date) {
    var d = new Date(date),
        month = '' + (d.getMonth() + 1),
        day = '' + d.getDate(),
        year = d.getFullYear();

    if (month.length < 2) 
        month = '0' + month;
    if (day.length < 2) 
        day = '0' + day;

    return [year, month, day].join('-');
}
//replace columns[2] with your desired column reference
columns[2] = formatDate(inputDate)
  • Meghan_550057
    Meghan_550057 Posts: 107
    Options

    I've run into this quite a few times as well and submitted a product request for a standard Tables action: https://community.pushbot.com/discussion/678/format-a-date-standard-action-for-columns-in-table-or-excel

Categories