iPad Numbers Date and Time format

s,

Not many of use here have iOS devices. You'd be better off posting in the iWork for iOS forum.

https://discussions.apple.com/community/app_store/iwork_for_ios

Jerry

I have encountered the same bug and it is indeed very annoying.

To make things worse I found another bug in the DATE/TIME function. Sometimes an incorrect day will display even though the day is correctly entered. For example, September 16 displays as September 11. Deleting the entry, which can be tricky itself, and re-entering the day sometimes will correct the display and sometimes will not.

As the ability to correctly enter, format and display dates is vital to most of my spreadsheets, I have been forced to abandon this app until the bug(s) get fixed if ever. Other spreadsheet apps in the store may not look as pretty but they get the job done and that is what's important.

BTW, your post is in the right place. I hope someone at Apple follows these discussions.

Just bitten by this bug myself, this is how I work around it. Not elegant but IMHO more robust.

1. Create a column for data entry, set format as string.

2. When entering data, follow this format:

Date: yyyymmdd (20110924 =>2011 Sep 24)

Time: hhmm (1745 => 17:45 = 5:45 PM)

3. [OPTIONAL] Create a !display only! column to 'beautify' the value by entering the formulas below.

Date: =DATE(MID(DateCol, 1, 4), MID(DateCol, 5, 2), MID(DateCol, 7, 2))

Time: =TIME(MID(TimeCol, 1, 4), MID(TimeCol, 1, 4), 0)

4. When/if you need to run any calculation on them, you have two options:

a. Use the 'beautified' column.

b. Create your own raw data formula.

Personally I went with 4.b, since I do not use the 'beautification' column.

@birdydon, is there any way you can share and/or reproduce the incorrect display?

If there is a bug with the actual end formatter, step 3 may be rendered useless and necessitates

the use of step 4b.

BTW, if you have a specific need to use some sort of financial app, I'm sure there are plenty already available.

Seems there is no edit button here.

After having another go at this problem, I think I have found a better solution that solves both formatting and date entry problem. The only downside is that you'll need to make 5 columns for a single date rather than 1.

Step 1.

--------

For each date entry, create the following columns:

| FormattedDate | NumbersDate | Day | Month | Year |

FormattedDate is the final date displayed according to our format.

NumbersDate is the internal Numbers date representation.

Day, Month, Year columns are the ones you use for data entry.

Step 2.

---------

Create/convert our 3 columns date (Day, Month, Year) into Numbers internal date.

So, in NumbersDate column, insert this formula:

NumbersDate: =DATE(Year, Month, Day)

Step 3.

--------

Apply our own formatting for the date output display. In this example, I want an output in the form of Dayname, Day Month Year (Saturday, 24 September 2011).

FormattedDate: =DAYNAME(NumbersDate) & ", " & (Day) & " " & MONTHNAME(Month) & " " & (Year)

This way, changing the raw date input is as simple as changing the respective column. If any discrepancy between entered and displayed data exist, it is easily spotted by comparing between the raw date columns and the formatted output.

Another advantage is reducing the risk of data corruption to the date, since the date is now simply represented as integer values in their respective columns.

@fruitful_taste

Sure.

I took a screenshot of of the problem and uploaded it to Dropbox.

The URL is http://dl.dropbox.com/u/283829/DateBug.PNG

You can see that the cell content is displaying Sept. 11. The dialog box is set to change this to the correct value of Sept. 16. Tapping on the Done button will clear the dialog and leave the incorrect value unchanged.

Strange is all I can say, certainly the date entry bug does not happen with mine (so far).

Anyway, here is a numbers document created in ipad that implements my second solution.

Feel free to copy the table/formula for your own use.

http://dl.dropbox.com/u/42982723/DateWorkaround.numbers

And the screenshots of the formulas here:

Step 2

Step 3

PS:

This is posted from my PC; I seriously can't believe how hard it is to post/reply via ipad. Also to make things worse, the nice formatting toolbar does not appear there as well !!!

I have always assumed the web experience on ipad is equal to the one on regular PC/Macs.

sbikes wrote:

According to the path above my initial post, I posted this discussion in the iOS Apps > iWork for iOS Discussion Forum. Am I missing something?

It could be that I strayed accidently into iOS land. In that case, I apologize.

Jerry