I would like to show an asterisk in a cell for 48hrs (eg. tentative booking) . The date is in cell A. Asterisk will be in cell B. I would also like to keep this asterisk there only if there is no tick in another cell eg. payment not made..

Hi Wendy,

Here is one approach that should do what you want.

Have a cell in a header or footer with the current time, =NOW(). Here it's in A1.

Add an elapsed hours column in your table, here column G, with this formula, filled down:

=DUR2HOURS($A$1-A)

This calculates the difference in hours between the date-time in column A on that row and the current date-time in A1.

(If you already have the current date-time set up in another cell, then substitute that cell's address for $A$1, being sure to include the $ to anchor the reference.)

If your column with the ticks (cells formatted as Checkbox) is F, then in column B where you want the asterisk try this formula, filled down:

=IF(AND(F=FALSE,G<48),"*","")

This says, show an asterisk if F is not checked and G is less than 48, otherwise show a "blank."

You can hide column G. Or if you don't like the clutter you can just get rid of it altogether by incorporating what it does into this longer formula in column B:

=IF(AND(DUR2HOURS($A$1-A)<48,F=FALSE),"*","")

SG

Hi Wendy,

There's not enough information there to ensure that my example matches your table, but you may be able to get some idea of how to accomplish what you want from it.

The Cell A1 shows the time and date at which the screen shot was taken. The cell also provides a convenient location from which to copy the date and time for pasting into one of the cells in column B. If you plan on doing this, I would suggest you format cell A1 as you wish the date and time to be formatted in column B (including font, size, 'normal', text colour and cell fill colour).

Column C contains the formula below, entered into C2, then filled down to the rest of the cells in the column:

C2: =IFERROR(IF(LEN(D)>0,"√",IF((NOW()-B)<DURATION(,2),"*","")),"")

The first IF checks for an enty in C (Deposit). If found, it places a check in the cell, confirming the booking. If there's nothing in column, the next IF is called. This one compares the booking time to NOW. If NOW is less than two days after the booking time, the formula places an asterisk in the cell, indicating a tentative booking. If NOW id more than 2 days after the booking time, the formula inserts a null string (which appears blank), indicating the accomodation is available.

Note that these changes will occur only when an entry is made in the table, triggering a recalculation of the table.

You've posted in the Numbers community, where the topic is Numbers for Mac. As there are differences between the Mac version and Numbers for iOS, the version that runs on your iPad, I've also requested this discussion be transferred to the iWork for iOS community.

Regards,

Barry

You've posted in the Numbers community, where the topic is Numbers for Mac. As there are differences between the Mac version and Numbers for iOS, the version that runs on your iPad, I've also requested this discussion be transferred to the iWork for iOS community.

Hi Barry,

Is there a way to show in both communities? Where do people who use both versions of Numbers (often syncing a spreadsheet back and forth) go? If Apple's strategy works (Numbers now free on every new iOS device) there will be a lot more of us.

In this case (and in most cases) the formulas work the same. On the iPad long formulas are tougher to enter, though.

SG

Hi Wendy,

I can see how you would be frustrated about not being able post a screen shot. Unlike Barry, I didn't notice you are working on an iPad.

In Safari on the Mac when you're composing a post here you see an edit toolbar above where you're typing. You click the camera icon, Choose File, navigate where you have the screen shot file, click Choose, click Insert Image. A little clunky, but not too bad.

But if you're posting from an iPad usng Mobile Safari there's no edit toolbar, no camera. I've just tried and it doesn't *seem* possible to post an image, at least not using Mobile Safari. I see that one person in this thread reported success on the iPad using a third-party browser called iCab Mobile. I don't have iCab so I don't know if that still works.

A sure bet is to use a Mac or PC to post the screen shot. On the Mac I was able to drag an iPad screen shot out of the photo stream in iPhoto or Aperture onto the desktop, then post it from Safari as described above.

From your description Numbers (on iPad or Mac) very likely can do what you want using Barry's or my approach. Few would describe spreadsheet date calculations as "something basic" though, so don't feel discouraged. When you manage to get a screen shot posted, we can take it from there.

SG

I have the NOW function in the date colomn A , i would also like to have this blank until I insert the guests name into column b.

Just one other quick thought pending screen shots. A formula like this in A generally should do what you describe:

This shows nothing in A if B is empty (there is no guest name). When you enter a guest name in B on that row it displays the current date-time. You would then need to immediately Copy>Paste>Paste Values that cell in A so it doesn't keep updating to the current date-time whenever you make a change somewhere on the spreadsheet.

SG

HI Wendy,

"I have the NOW function in the date column A , i would also like to have this blank until I insert the guests name into column b."

Is this the cell that is to contain the Booking time? If so, remember tthat NOW updates with each change to the table. To prevent the date and time changing, follow the copy/Paste Values instructions below.

Try this formula in column A: =IF(Len(B)>0,NOW(),"")

After the guest name is entered (and the date and time appear in column A), Copy the cell containing the NOW() function, then use Edit > Paste Values to paste the fixed Date and Time value back into the same cell, replacing the formula with the last calculated value.

Regards,

Barry

Hi Wendy,

This is a lengthy post due to the detail I've gone into. You've expressed a desire to learn, hence the detail. Take it slowly, Note that some of this is based on my interpretation of the formulas as they appear in the screenshots. As mentioned below, I don't have or use an iOS device, so I'm unable to test what I've written below. Let me know how what I've said fits in with your observations.

B

Just saw your screen shots, with SG's formula and mine.

The Syntax error is the missing parenthesis after A5 (and the extra one at the end of the formula).

Should be: =IF(AND(DUR2HOURS(A5)<48,O5=FALSE),"*","")

With that corrected, you'll get another error message regarding DUR2HOURS. This function changes a Duration value to a number representing the number of hours equivalent to that duration. Cell A5 contains a Date and Time value, not a duration, and the function will return an error message, "The function DUR2HOURS expects a duration, but cell A5 contains a date."

Revise the formula to read: =IF(AND(DUR2HOURS(NOW()-A5)<48,O5=FALSE),"*","")

Regarding the second example:

Copying the formula as it appears, I see several errors and possible errors.

=IFERROR(IF(LEN(ISBLANK(O8)),""*"",IF(NOW, "" ()-A8<DURATION((,2),""*",""")),""""),)),)

iOS appears to show an opening parenthesis— ( —using the concave end of a function name if it is to contain the argument(s) for the function, and as 'crescent moons' when they do not directly follow a function name (no examples in this formula). Closing parentheses— ) — are always shown as 'crescent moons.'

Any parentheses that display as ordinary type ( such as those enclosing ',2' after DURATION) are either intended to be text, or are placed in error.

Functions that take no argument, such as NOW, display with convex curves at both ends, suggesting that while parentheses are required after these functions in OS X, they are not needed (and likely not allowed) in iOS.

LEN(ISBLANK(O8))

ISBLANK(O8) looks at cell O8, and if it is blank (empty), returns TRUE, or if it contains anything, returns FALSE. A checkbox cell is never empty; if checked, it contains TRUE, if unchecked, it contains FALSE. ISBLANK will always return FALSE. LEN measures the length in characters of whatever is in its parentheses—in this case FALSE, so it will always return 5.

If testing a checkbox, it's sufficient to use only the cell address of the checkbox, then arrange the 'do-if-true' and 'do-if-false' arguments to suit (checked=TRUE, unchecked=FALSE).

The white lozenges in the formula contain text strings. I am guessing they are generated by tapping the "abc" button visible in the screen shot, and that tapping the button inserts the lozenge with a pair of double quotes already included. Only the text that you want to appear should go between those quotation marks. Most of these lozenges appear to have an extra pair of double quotes.

Commas are used to separate arguments in a function. Their placing is critical.

Here is a revision of the formula shown above. I don't use an iOS device, so I am unable to test it. Remember that all of the left parentheses - ( - will be added automatically, and will display as concave right ends on the boxes containing the function names.

=IFERROR(IF(O8,"",IF(NOW-A8<DURATION(,2),"*","")),"")

In English:

If O8 is checked (TRUE), this cell will appear empty; otherwise...

If the date & time in A8 is less than two days earlier than NOW, this cell will show an asterisk; otherwise...

This cell will appear empty.

If any of these steps generates an Error*, this cell will appear empty.

*Comparing NOW-0 to a duration will generate an error until there is a date and time vaue in A8. IFERROR is used to trap this error.

Regards,

Barry

Hi Wendy,

"Goodness Barry, I am not doing very well at this ! See how I have 2 brackets at the end, would upset it cause I

cant seem to get rid of one with out the whole formula selecting and going when I tap delete or backspace."

Numbers adds as many parentheses at the end of a formula as are needed to balance the number of opening - ( - and closing - ) - parentheses. Unfortunately, if you then examine the formula and place missing parentheses where they should be, Numbers doesn't automatically remove the extras.

I discussed this in greater detail in connection with my formula in the post above, so this will be prety short.

Add a closing parenthesis after B6 and before the = sign.

Delete both parentheses after NOW. These are apparently not needed in Numbers for iOS.

Delete one of the parentheses at the end of the formula (it's the one you inserted after B6).

"I cant seem to get rid of one (bracket) without the whole formula selecting and going when I tap delete or backspace."

In Numbers for Mac, you need to make sure the insertion point is visible in the entry box, and is located where you want to insert or delete something, before prssing delete. I don't know that Numbers for iOS has an insertion point (aka a 'cursor' in the Windows world), but I suspect it has, and the same 'rule' is true there. Placing the insertion point in the box may require a second tap after opening the box.

Regards,

Barry

Hi Wendy,

After reading your and Barry's latest posts I see that some (most) of the problem is that, although the formulas in the Mac and iOS Numbers are exactly the same, the way they are entered and displayed on the iPad is different enough in small ways to cause understandable confusion.

On the Mac we are used to typing in formulas just the way you see them in Barry's and my examples. On the iPad you're constructing formulas in an interface that requires slightly different habits.

For example, one difference that tripped me up was how to enter "", meaning null or empty. Like what I think I see in one of your screenshots, I kept getting getting """", causing Numbers to complain about a formula error. Instead of tapping the 'abc' key and typing "", as one would expect, just tap the "abc" key, then tap 'Done' without typing anything in the 'Enter Text:' box. That will insert "" into the formula.

For example,

=IF(Len(B)>0,NOW(),"")

Should look like this:

On the iPad you should never see a "(" by itself. Nor should you see a ")" in black. As Barry describes (without an iPad!) all you should see is concave ends of function names and closing parentheses shown as "crescent moons."

One thing that could make it easier to get help about formulas on the forum: Just open your document in Numbers for iCloud beta (if you've enabled iCloud in the app, then you can sign into icloud.com and navigate to Numbers there, where you should see your documents). Formulas look almost exactly the same there as on the Mac and in the Reference Guide, and you often can enter them in more easily than on the iPad, i.e. just type them in as you see them described. They'll sync back to the iPad where they then display iOS style with those "crescent moons" and all that.

So maybe try some of Barry's and my ideas that way, then pick and choose what seems to work best for you. One step at a time.

SG

Forgot to mention I don't think you can run Numbers for iCloud beta from the iPad in Mobile Safari. It runs on Safari on the Mac and, I understand, in other modern browsers on the Mac or Windows PC. Maybe try the same browser used to post the screenshots to the forum.

SG