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 ;)