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 

TIP: 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?