How to convert a text date into a Sigma date type using Substring and MakeDate

Say you have an oddly-formatted date in a text (string) type, but need Sigma to read it as a date type. We’re able to extract the relevant values for Day, Month, Year etc. using Substring() function and then, with a bit of additional cleaning, input them into the MakeDate() function.

Example Text Dates:
Tue Feb 09 2021 20:17:20 GMT-0600 (Central Standard Time)
Wed Mar 03 2021 16:27:47 GMT-0500 (Central Daylight Time)


Number(Substring([CreatedAt], 9, 2))


Switch(Substring([CreatedAt], 5, 3), “Jan”, 1, “Feb”, 2, “Mar”, 3, “Apr”, 4, “May”, 5, “Jun”, 6, “Jul”, 7, “Aug”, 8, “Sep”, 9, “Oct”, 10, “Nov”, 11, “Dec”, 12)


Number(Substring([CreatedAt], 12, 4))


MakeDate([Year], [Month], [Day])