topic Re: Changing a date and time column to yield only the date. in Discussions

Changing a date and time column to yield only the date.

ileshem — Wed, 21 Mar 2018 20:59:33 GMT

Hello again,

I would like help with changing a date and time column to yield only the date.

I have changed the date and time column to show only the date via the column info but when I'm tring to make a vairability chart using the date as an X axis, I am still getiing the date and the time.

Thanks in advance.

Re: Changing a date and time column to yield only the date.

cwillden — Wed, 21 Mar 2018 22:48:32 GMT

hi @ileshem,

Data formats don't change the actual underlying values, just how they are displayed. There may be a better way to do this, but I often just create a new column where I take the value in the original data-time column and format it with a column formula like so:

Format(:Column 1, "m/d/y")

This drops the time data so that it is effectively changed to the 12:00:00 am. Then, I delete the column formula and change the new column's format to the desired date format in Column Info.

Re: Changing a date and time column to yield only the date.

vince_faller — Thu, 22 Mar 2018 23:33:56 GMT

could always turn the time in seconds to days and floor it then turn it back into seconds

x = today();
show(MDYHMS(x));
show(MDYHMS(floor(x/(60*60*24))*60*60*24));

Re: Changing a date and time column to yield only the date.

Jeff_Perkinson — Fri, 23 Mar 2018 19:39:07 GMT

Datetime values are stored as the number of seconds since January 4, 1904 00:00:00 (midnight).

Date values are also stored as the number of seconds since midnight, January 4, 1904 to midnight on the date in question.

Since both values are stored as seconds, you just need to subtract the time of day portion from a datetime value to get the date value.

//today() returns the current date and time
datetime=today();

//subtract the time portion of datetime
date=datetime - time of day(datetime);

//show datetime and date as seconds and then format date to see the date
show(datetime, date, format(date, "mm/dd/yy"));

This can also be done in the formula editor to create a new column with the date. The easiest way is to right click at the top of the datetime column and choose New Formula->Date Time->Date.