All Collections
Custom Functions
Date Formatting with Custom Functions
Date Formatting with Custom Functions

Learn how to quickly format the datetime output into over 50 different formats in 2 seconds using formatDates

C
Written by Chris Ware
Updated over a week ago

Overview

If you've ever spent time as a professional investment analyst, trader, portfolio manager or similar then you know that "pain" is another word for re-formatting date time data in columns 

You can load this template in one click from the add-in using a Templates widget inside the workspace section

Usage

For any Cryptosheets Core custom function you can simply add the _formatDates argument to any formula and then define the date format output you want to display.

TIP: you can define multiple different formats separated by commas to display multiple different formats for the same datetime value

  • This can be a helpful reference to convert datetime data from other/multiple datasets

  • You can use this to quickly build multiple aggregations that require specific datetime formats

FormatDates - Reference Table

parameter      |    example    |  description

BASIC & COMMON

MM-DD-YYYY      |    12-10-2019    |  Date with -

MM/DD/YYYY      |    12/10/2019   |  Date with /

YYYY-MM-DD      |    2019-12-10    |  Date w/ yr first

MM-DD-YYYY hh:mm:ss.000     

LT      |    8:30:00 PM    |  Time

LTS      |    8:30:25 PM    |  Time with seconds

L      |    9/4/1986    |  Month numeral, day of month, year

l      |    9/4/1986    |  

LL      |    4-Sep-86    |  Month name, day of month, year

ll      |    4-Sep-86    |  

LLL      |    9/4/1986 20:30    |  Month name, day of month, year, time

lll      |    9/4/1986 20:30    |  

LLLL      |    Thursday, September 4, 1986 8:30 PM    |  Month name, day of month, day of week, year, time

llll      |    Thu, Sep 4, 1986 8:30 PM    |  

NUMERICAL DATE REPRESENTATIONS (HINT: These can be VERY POWERFUL)

M      |    1 2 ... 11 12    |  Month

Mo      |    1st 2nd ... 11th 12th    |  

MM      |    01 02 ... 11 12    |  

MMM      |    Jan Feb ... Nov Dec    |  

MMMM      |    January February ... November December    |  

Q      |    1 2 3 4    |  Quarter

Qo      |    1st 2nd 3rd 4th    |  

D      |    1 2 ... 30 31    |  Day of Month

Do      |    1st 2nd ... 30th 31st    |  

DD      |    01 02 ... 30 31    |  

DDD      |    1 2 ... 364 365    |  Day of Year

DDDo      |    1st 2nd ... 364th 365th    |  

DDDD      |    001 002 ... 364 365    |  

d      |    0 1 ... 5 6    |  Day of Week

do      |    0th 1st ... 5th 6th    |  

dd      |    Su Mo ... Fr Sa    |  

ddd      |    Sun Mon ... Fri Sat    |  

dddd      |    Sunday Monday ... Friday Saturday    |  

e      |    0 1 ... 5 6    |  Day of Week (Locale)

E      |    1 2 ... 6 7    |  Day of Week (ISO)

w      |    1 2 ... 52 53    |  Week of Year

wo      |    1st 2nd ... 52nd 53rd    |  

ww      |    01 02 ... 52 53    |  

W      |    1 2 ... 52 53    |  Week of Year (ISO)

Wo      |    1st 2nd ... 52nd 53rd    |  

WW      |    01 02 ... 52 53    |  

YY      |    70 71 ... 29 30    |  Year

YYYY      |    1970 1971 ... 2029 2030    |  

Y      |    1970 1971 ... 9999 +10000 +10001    |  

``      |    Note: This complies with the ISO 8601 standard for dates past the year 9999    |  

gg      |    70 71 ... 29 30    |  Week Year

gggg      |    1970 1971 ... 2029 2030    |  

GG      |    70 71 ... 29 30    |  Week Year (ISO)

GGGG      |    1970 1971 ... 2029 2030    |  

TIME SPECIFIC

A      |    AM PM    |  AM/PM

a      |    am pm    |  

H      |    0 1 ... 22 23    |  Hour

HH      |    00 01 ... 22 23    |  

h      |    1 2 ... 11 12    |  

hh      |    01 02 ... 11 12    |  

k      |    1 2 ... 23 24    |  

kk      |    01 02 ... 23 24    |  

m      |    0 1 ... 58 59    |  Minute

mm      |    00 01 ... 58 59    |  

s      |    0 1 ... 58 59    |  Second

ss      |    00 01 ... 58 59    |  

S      |    0 1 ... 8 9    |  Fractional Second

SS      |    00 01 ... 98 99    |  

SSS      |    000 001 ... 998 999    |  

SSSS ... SSSSSSSSS      |    000[0..] 001[0..] ... 998[0..] 999[0..] 

Z      |    -07:00 -06:00 ... +06:00 +07:00    |  

ZZ      |    -0700 -0600 ... +0600 +0700    |  
z or zz      |    EST CST ... MST PST    |  Time Zone

X      |        |  Unix Timestamp

x      |        |  Unix Millisecond Timestamp


You're welcome ;)

Did this answer your question?