How to change a Text columns date format?

Jozef_783863Jozef_783863 Posts: 263 admin
edited February 11 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

Answers

  • Jozef_783863Jozef_783863 Posts: 263 admin
    edited February 11
    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_111003kevin_111003 Posts: 36 admin
    edited February 11

    @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)
    
    
  • 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

Sign In or Register to comment.