Let’s suppose I have the following simple table:
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:
- 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’
- Set the ‘Date’ format to ‘Date’
- Set the ‘Time’ format to ‘Time’
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:
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:
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: