Auto-populating Cells

Question

Level 1

4 points

Auto-populating Cells

Hello. In the Cell data format fields, I have formatted the data cells in columns A and B as Date (Time: None) and Time (Date: None) respectively, i.e.:

I expected the current date and time to auto-populate each cell (based on the Mac's clock) when I tabbed out to the next cell. LOL. Clearly, that did not happen. Is there a way (besides writing a macro in AppleScript) to have that happen in these two columns?

Currently, I am using TextExpander defaults (<ddate> and <ttime>) to populate the date and times in these cells. BUT it would be much easier if the template I have created already contained the auto-population of these columns once it opened. There will be at least three— or possibly more— entries per day with differing data in each row of seven columns (total: 150 rows of seven columns each). Please let me know if my question is unclear (stroke victim, I am).

Also, I would like to make a chart using just the first 3 columns of data: Date-Time-<numerical data>. Is there a way to limit Numbers to using just these three columns in chart creation?

Thanks for any response— Keith

Posted on Dec 13, 2017 12:50 PM

Reply

Answer 1

Best reply

Wayne Contello

Level 7

26,529 points

Dec 13, 2017 6:53 PM in response to keethie

Keith,

formatting a cell causes the cell to use information in the cell (which there is none in your case), and present it in the format you selected.

An example is you can type:

1/1/16 for a date.

there are several, valid ways, to show the date above. here are some:

1 Jan 16

1 Jan 2016

1 January 2016

January 1, 2016

etc.

You can choose the format you prefer from the pop-up menu

If you select formatting for a data/time value, then the date you enter will be reformed to conform to you selected format. Formatting does not cause content to appear in a cell.

you could select one of those cells, then enter for formula:

=today()

based on the screenshot, I think I would recommend typing the date in and letting Numbers format the date. I would not try to get fancy and have Numbers enter the date for you.

the function I presented earlier (=today()) will update when the day changes. if you enter this formula in every cell, then information you enter on each row, representing information for a particular day, will get confused when every row shows the same date.

as a shortcut, you can enter the day and month... like this:

"12/13"

and Number will infer that you mean

"12/13/2017" since the year today is 2017.

Reply

Answer 2

SGIII

Level 8

35,258 points

Dec 13, 2017 2:15 PM in response to keethie

Numbers cells formatted as Date & Time always contain a date-time string but can be formatted to display just the date or just the time, or both.

If you put the function NOW() in the cells they will contain the date-time when you made the last change to your document.

If you put the function TODAY() in them they will contain the date-time at the beginning of the day when you made the last change (i.e., today's date with 0 for the time portion of the date-time string.

Note that these formulas update every time you make a change to the document. So if you want no further changes you need to select the cells, command-c to copy, and Edit > Paste Formula Results to convert the to unchanging values.

If you are looking for an convenient way to "time stamp" cells you may find this Today Automator Service (Dropbox download) is easier than using formulas.

To install the service just double-click the downloaded .workflow package, give permission at System Preferences > Security & Privacy and also make sure 'Automator.app' is checked at System Preferences > Security & Privacy > Privacy > Accessibility.

Thereafter to use you just select the cell or cells where you want a date-time stamp and choose 'Today' from the Numbers > Services menu. (The 'Today' is actually a misnomer; it inserts the current date-time). Then format the cells to show just date, just time, etc.

SG

Reply

Answer 3

Barry

Level 9

59,861 points

Dec 13, 2017 11:12 PM in response to keethie

HI Keith,

Regarding your chart:

How the results are shown on your chart depends what you want the chart to show.

What you want to show will determine how the results are recorded.

If you want to display the result as a single line, then you will need to use two columns of data—date and time, and glucose.

This will permit you to construct an xy scatter graph with data points connected by straight lines or curves.

The line will show glucose levels at each D&T point, connected by a single line (or curve).

If the three times are reasonably consistent from day to day, you might consider using a vertical bar graph with three series—one for each of the three times.

Or you could use an xy scatter chart with four series—one showing all the glucose values, one showing only the first daily record, one showing only the second and one showing only the third.

This could provide a single line or curve (connecting the points in the 'complete' series, for which there would be no markers for the data points), the others showing only the markers for the three sets of data points with no lines or curves connecting them.

For examples of these charts, please provide a sample data set for a seven day period. The data should be realistic, but does not need to be a 'true' sample.

Regarding the data recording table:

Date and Time is a single datum for each instant in time. Is there a good reason to split the presentation into two cells?

Regards,

Barry

Reply