Split 'Date and time' into 'Date' and 'Time' columns

1

Let’s suppose I have the following simple table:

image
image654×679 15.7 KB

If I want to have the ‘Date and time’ values be in separate ‘Date’ and ‘Time’ columns, one approach is to duplicate the column values:

image
image984×695 16.9 KB

Then

  • Set the time portion of all the ‘Date’ values to ‘12:00 AM’
  • Set the date portion of all the ‘Time’ values to ‘12/30/1899’

image
image942×692 25.1 KB

And finally:

  • Set the ‘Date’ format to ‘Date’
  • Set the ‘Time’ format to ‘Time’

to get:

image
image666×586 13.7 KB

The really tedious part in these steps is manually setting a whole column of date and time values to have a value of ‘12/30/1899’ for just the date portion (leaving the time alone). Similar for setting the time to ‘12:00 AM’ for a whole column.

My question is, is there a more straightforward way to set the date portion of an entire column instead of the manual approach described above?

Why would I want to keep the date and time in separate columns? So that I can easily group by each to get the following view:

image
image751×1059 16.7 KB

For example, I have a nutrition tracker where it’s nice to know how many of each macro nutrient was consumed in a day and in a meal. (A “meal” is considered to be a set of foods consumed at the same time.) This view allows for this:

image
image2348×1587 242 KB

I had considered storing the date and time in a single column and having formula columns that extract the date and time and then grouping by those. However, I think having these synthetic helper columns would mean that I wouldn’t be able to add records into groups (which is nice).

Here’s a test doc with the simple example tables shown earlier:

2

Hello there @Eduardo_Cavazos!
If you want to keep adding as Date and Time and also have them separate for grouping then the way to go is adding the Date column and Time column and using the next formulas:

Date column:
=thisRow.[Date and Time].ToDate()
Time column:
=thisRow.[Date and Time].ToTime()

image

1 Like
3

Hey @Saul_Garcia !

The downside to grouping based on formula columns is that the resulting view does not appear to allow for easily adding items to the groups (grouping by separate date and time columns does). So I really was going for an approach where I convert a single column to two other columns and deleting the original ‘date and time’ column.

However, your approach suggests one way to do it:

  • Create a new column ‘Date Extracted’ using the formula =thisRow.[Date and Time].ToDate()
  • Create a new column ‘Time Extracted’ using the formula =thisRow.[Date and Time].ToTime()
  • Create a new column of format Date. Copy the values from ‘Date Extracted’ into this row.
  • Create a new column of format Time. Copy the values from ‘Time Extracted’ into this row.
  • Delete the columns ‘Date and Time’, ‘Date Extracted’, and ‘Time Extracted’.

It’s a few steps but it’s still easier than manually setting a whole column of date and time values.

Thanks for your suggestion!

4

I see, is there a reason to have the Date and Time column?
If somehow that comes from another place you are importing to your document you could have an Import Table with Date and Time in the same column and another Table with the Date and Time in different columns, and with the help of a button you could import from the first table to the second table and then delete the row from the first table.

5

Nah, my aim isn’t to have a ‘date and time’ column in addition to the individual ‘date’ and ‘time’ columns.

There’s been a few cases where I’ll throw together a table quickly with a ‘date and time’ column and then later down the road wanted to convert that to separate columns. So I was just wondering about a more efficient way to do that. :slight_smile: