Static date on Checkbox TRUE?

Numbers cannot do what you want. There is a work around where you keep a cell with now in it that will update anytime you make a change. Then you can copy, then paste value using the menu item “Edit > Paste Value”:

A1=NOW()

not as automatic as you hoped and still usable. You can even place the "=now()" in a single cell table so you can move it around to keep it nearby

You can provide feedback to Apple regarding this feature or other using the menu item "Numbers > Provide Numbers Feedback"

Hi Cody,

Credit to Wayne:

https://discussions.apple.com/thread/3734500?start=0&tstart=0

You need to copy the date and then (in another Cell) Edit > Paste and Match Style.

In this screen shot, I used NOW() that shows Date and Time (because I can't hang about until tomorrow to see if it works). I did the Paste and Match Style at 1:43 AM and took this screen shot at 1:52 AM. Cell E2 has held the TimeStamp.

Your formula would be [note the () after TODAY]

=IF(C2, TODAY(), "")

Bravo Wayne.

Regards,

Ian.

I have tried this, but end up with an error (for cell E2):

Argument 1 of IF expects a Boolean, but cell D2 contains a date.

Any idea what I have done wrong?

Thanks,

Peter

"Any idea what I have done wrong?"

Hard to tell, without you telling us what you did.

According to the error message, your IF statement may look like this:

IF(D2,do-this,do-that)

If cell D2 contains the value TRUE, then IF will do-this. If cell D2 contains the value FALSE, then IF will do-that.

But if cell D2 contains any other value, IF will generate a error message similar to the one you got.

In your case, D2 contains a Date and Time value (and displays only the date part).

Note that Ian's example references cell C2, not D2, and that C2 in his example contains a checkbox. Checkbox cells contain FALSE if unchecked, and TRUE if checked.

Rgards,

Barry

I'm trying to find a way to hold a static date in a cell derived from the NOW() or TODAY() function in a different cell. Ian seems to have accomplished this, although I get the error message instead.

Peter

Thank you, Barry, for your reply.

I am working with Numbers '09. I understand your message, but if I reference cell C2, what shows up in cell E2 is another check box.

What I'm trying to do is find a way to change a date derived from the Now() or Today() function to a static date. Ian seemed to accomplish this in his example.

Peter

Peter,

A cell containing NOW() or TODAY() can not be converted to a fixed date programmatically, except perhaps via Applescript.

Here's what I would do:

Select the cell

Hit the Delete key

Type a keyboard shortcut for Insert > Date and Time

I have assigned Shift-Option-Command-D to the Date & Time selection in the Insert menu. That assignment is done in System Preferences, Keyboard, Keyboard Shortcuts. I mention my choice for shortcut because it's been working for me for years, so it seems to be a robust combination that doesn't interfere with any other selection.

Jerry

Thanks Jerry,

I suspected this, although Ian seemed to have a work-around. I still don't understand how he got his cell E2 to hold his Time Stamp.

I'll go with inserting Date and Time.

FWIW, I've also tried changing a numeric value to text, figuring that if I could do this, I might be able to convert the text back to a numeric value. (This might have provided me a means to set a date in a cell which was the numeric value greater than the date in the previous cell in the column, yet not change when the actual date changed.) This doesn't seem to work in Numbers, however, because the text versions of the numbers continued to behave like numerals, rather than text.

It would be terrific if Apple updated Numbers to program in static dates. I know this can be done in Excel. Does Apple have any interest in improving Numbers capability?

Peter

Peter,

Apple have announced a new version by the end of the year, or so it seems. I didn't know you could freeze TODAY or NOW with Excel, but it doesn't exactly surprise me. If Excel is the big industrial app, Numbers is the little boutique app. I suppose that Apple would like to improve Numbers, but what they would consider to be an improvement would be difficult to say. I'm pretty sure that making Numbers work just like Excel wouldn't be considered an improvement.

You can go to the Numbers menu and choose Submit Feedback to let Apple know your opinion as to how to improve Numbers.

Jerry

Thanks Jerry.

I'm not expecting Apple to recreate Excel. (I have worked with macros in the past and actually found them quite useful.)

But a few useful tweaks here and there could make Numbers a lot more powerful. I have no idea about how much code would be required, but providing an option for static dates would be really helpful (to me at least.)

I'll try submitting feedback as you suggest.

Peter

Peter Weiler wrote:

I am working with Numbers '09. I understand your message, but if I reference cell C2, what shows up in cell E2 is another check box.

What I'm trying to do is find a way to change a date derived from the Now() or Today() function to a static date. Ian seemed to accomplish this in his example.

Hi Peter,

As I said in my earlier message, I can't tell you what you are doing wrong if you don't tell me what you are doing.

What is the formula you are using in th first sentence quoted above? Where is that formula located?

Ian (and Wayne) accomplished what you are asking by using a three step process:

1. Use a formula to calculate the value when the checkbox is checked.
2. Select the cell with the calculated value and Copy.
3. Select the cell to contain the fixed value, then go Edit > Paste Values.

From Ian's description and screen shot:

C2 contains the checkbox.

D2 contains the formula: =IF(C2, TODAY(), "")

Ian selects and copies cell D2, then selects cell E2 and pastes the calculated value into cell E2 using Edit > Paste Values.

The value in D2 will change every time there is a change in the table (if the checkbox remains checked).

The value in E2 will remain the same until it is changed by the user.

From Wayne's description and screen shot:

The checkbox in C2 does not play a part in the process.

The formula in A1 (first example) or in A1 of a separate table (second example) is =NOW(), or =TODAY() (if you want only the date displayed).

Wayne selects and copies the cell containing the formula (A1), then selects D2 and goes Edit > Paste Values.

The value in A1 will be recalculated with each change in the spreadsheet.

The value in D2 will remain the same.

Jerry's method omits the cell containing a formula, instead using the Insert > Date and Time menu item, or the key press combination he constructed for this menu item.

Once generated and inserted, the value remains the same until changed by the user,

Either of these methods will work. Your choice depends on which you find more convenient.

Regards,

Barry

Hi Peter,

In my example (credit to Wayne) I was trying to demonstrate that the timestamp did stick. The timestamp is in Cell E2. I used Cell D2 to show that the non-static date changed (it is not a timestamp). D2 is a timestamp because it was "locked in" by Copy then Edit > Paste and Match Style.

Regards,

Ian.

Aha!

I misled you. It is Paste Values, not Paste and Match Style that locks it in.

Cell B2 contains =NOW() and changes each time the table changes.

I copied B2, then in C2 I did Paste Values, in D2 I did Paste and Match Style

Note how B2 and D2 have updated, but C2 is static

C2 is the timestamp you are after, and you can get it by typing =NOW() or =TODAY() in the cell where you want your timestamp. Immediately Copy that cell and Paste Values into that cell.

Sorry for my mistake.

Regards,

Ian.

Barry and Ian,

Thank you for your explanations. After reading Barry's detailed response, I realized that I needed to use "Paste Values" instead of "Paste and Match Style." This made the date lock in.

FWIW, I have created a Numbers spreadsheet to help me monitor my water usage. A bill arrived last month that was triple what we had ever paid before to our water company. I investigated and discovered that our irrigation timer had reset one of the stations so that it was watering every day for an hour instead of once per week for 20 minutes. It must have happened during a brown out. Because of the way our property drains, I had not noticed the extra water. I reprogrammed the irrigation timer immediately.

Keeping track of our water usage is tricky because the billing is in gallons, but our water meter measures in cubic feet. The spreadsheet made this a lot easier to track. Each day I would open the water meter and write down the reading, then add it to my spreadsheet along with the date. My spreadsheet displayed an ongoing average gallons used per day. As I was in mid cycle, I needed to reduce the average used by a 1000 gallons daily. For the first few weeks, I took readings daily. I let the spreadsheet automatically update the date, by adding one to the previous entry in the date column. This worked fine until I skipped a few days.

What I wanted to do was enable the spreadsheet to enter the correct date automatically each time I updated it with a water meter reading. It would be easy to do this using the Insert Menu Date & Time, but I wanted to try to get it to do it automatically. This led to my questions about static dates.

I would be happy to send you a copy of the spreadsheet, though I don't know how to attach a file in these Discussions. If you are interested, let me know and I will send it to your email addresses.

Again, thank you very much for taking the time to help me out.

Peter

Hi Peter,

Without going to AppleScript (or possibly Automator), I don't think there is a way you can fully automate date stamping a new row.

My sympathy on your tripled bill! I had a similar occurence some years ago, caused by a leak between the meter and the house. Fortunately, our water district has a fairly forgiving attitude, and an alert billing staff who phoned the day the bills were being processed with a heads up. don't recall whether finding the leak or getting the phone call came first, but I do know that when they found out that we had acted promptly to repair the leak, the leak forgiveness poicy kicked in, and our bill was reduced to reflect our 'normal' consumption for the billing period.

You might try some negotiations with your water supplier.

Regards,

Barry