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