Using System Column in Dynamic View

Guaca Mohle

I have created a dynamic view which references the system column MODIFIED. Unfortunately, the optics of that column are not optimal. See screenshot. Any suggestions how to get this to appear merely as a standard DATE value?

MCorbin

Create another column.

Formula: =DATE(VALUE("20"+MID(Modified@row,7,2)),VALUE(MID(Modified@row,1,2)),VALUE(Mid(Modified@row,4,2)))

What you need to know about using Created and Modified dates:

These dates are displayed in local time, but behind the scenes, they are stored in UTC Time. While the displayed date will be correct, if used in a formula, the formula calculates and displays based on UTC (so in Pacific time, at 4 PM every day, the dates change to tomorrow's date). This is why I don't use the Dateonly() function that pulls the date from these columns - I use the formula above to make sure I get the correct date.

(It gets even more complex in that the DATE() function has a Y2K bug - if you only feed it a 2 character year, which is all you have in the Created and Modified columns, it defaults to 1900, so you have to hard-code the century. Technically, if this formula is still around on 12/31/2099, you'll have a Y2.1K type problem.....)

MCorbin

Create another column.

Formula: =DATE(VALUE("20"+MID(Modified@row,7,2)),VALUE(MID(Modified@row,1,2)),VALUE(Mid(Modified@row,4,2)))

What you need to know about using Created and Modified dates:

These dates are displayed in local time, but behind the scenes, they are stored in UTC Time. While the displayed date will be correct, if used in a formula, the formula calculates and displays based on UTC (so in Pacific time, at 4 PM every day, the dates change to tomorrow's date). This is why I don't use the Dateonly() function that pulls the date from these columns - I use the formula above to make sure I get the correct date.

(It gets even more complex in that the DATE() function has a Y2K bug - if you only feed it a 2 character year, which is all you have in the Created and Modified columns, it defaults to 1900, so you have to hard-code the century. Technically, if this formula is still around on 12/31/2099, you'll have a Y2.1K type problem.....)

Guaca Mohle

thank you!