Sunday, June 16, 2019

#1 2019-04-14 08:16:13 am

vw-as
Member
Registered: 2015-08-25
Posts: 6

Formatting date without time in Numbers

This concerns opening a simple Excel file in Numbers.

I wish to format the table with AppleScript (change width of columns, changes some cells to bold, etc.)

One column consists of dates.

On opening the Excel file in Numbers the dates are displayed as year / month / day (2019/4/14).

If run the AppleScript, for example:

Applescript:

set format of cell "A3" to date and time

then that date cell is converted to 14/04/2019 01:00 (based on an UK region set in System Preferences)

and running the appleScript:

Applescript:

get the format of range "A2"

returns “Date and Time”

However, I cannot see how it is possible to lose the time element “01:00”.

Is it possible to do this with appleScript? I know it is possible to do this manually in Numbers.

Any help would be gratefully received.

Offline

 

#2 2019-04-14 09:48:07 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3458

Re: Formatting date without time in Numbers

The dictionary clearly state :

format (automatic/checkbox/currency/date and time/fraction/number/percent/pop up menu/scientific/slider/stepper/text/duration/rating/numeral system) : The format of the range's cells.

So, as far as I know, we have no built-in way to select a sub-format.

We may use a not too bad scheme.
Create a document containing cells formatted with the wanted formats of our choice.
Ask the script to enter this document, select the cell with the wanted format.
Use some GUI scripting to copy the format of the cell.

Enter the main document, select the cells which must be formatted, use some GUI scripting to paste the format.

Here is a code doing the job.

Applescript:

set theSource to "our Formats.numbers" # Edit to fit your needs

tell application "Numbers"
   activate
   tell document theSource
       set active sheet to sheet 1
       tell sheet 1 to tell table 1
           set selection range to range "B4:B4" # Edit to fit your needs
       end tell
   end tell
end tell
tell application "System Events" to tell process "Numbers"
   set frontmost to true
   tell menu bar 1 to tell menu bar item -2 to tell menu 1 to click menu item "our Formats.numbers" # brings the doc to front
   keystroke "c" using {option down, command down}
end tell
delay 0.2 # REQUIRED

set theTarget to "the main.numbers" # Edit to fit your needs
tell application "Numbers"
   tell document theTarget
       set active sheet to sheet 1
       tell sheet 1 to tell table 1
           set selection range to range "C2:C10" # Edit to fit your needs
       end tell
   end tell
end tell

tell application "System Events" to tell process "Numbers"
   set frontmost to true
   tell menu bar 1 to tell menu bar item -2 to tell menu 1 to click menu item theTarget # brings the doc to front
   keystroke "v" using {option down, command down}
end tell

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) dimanche 14 avril 2019  17:46:36

Offline

 

#3 2019-04-14 06:22:01 pm

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4879

Re: Formatting date without time in Numbers

vw-as wrote:

However, I cannot see how it is possible to lose the time element “01:00”.


Hi.

With Numbers 6.0, I can only get the time to show if the cell's format's set to 'date and time' before the text is entered or if the cell's value set directly to an AS date. (Numbers's default 'date and time' format includes the hours and minutes.) Otherwise, if text resembling a date is entered into a cell having 'automatic' format (the default), the 'automatic' format adapts to show a date in Numbers's nearest equivalent to the format in the text. And getting the cell's value returns an AS date. Setting the cell's format to 'date and time' after that hardens the format explicitly to 'date and time' rather than a modified 'automatic'. (Hope this makes sense!)

Unfortunately, if your input text's in yyyy/m/d format, you have to set the cell format to 'date and time' first to get the text to be recognised as a date. (But see a retraction of this statement in the edit below.) But this seems getroundable — in Numbers 6.0 at least.

Demo:

Applescript:


tell application "Numbers"
   activate
   set newDoc to (make new document)
   tell table 1 of sheet 1 of newDoc
       -- Preset column "A"'s cell format to 'date and time'.
       set format of range "A2:A22" to date and time
       -- Enter dates in yyyy/m/d format.
       repeat with i from 2 to 22
           set value of cell i of column "A" to "2019/4/" & i
       end repeat
       -- Delay a couple of seconds to see the result.
       -- On my machine, the dates in column "A" are now shown in "dd/mm/yyyy hh:mm" format (ie. including the time), which is the default on my machine.
       -- The cell values are AS dates.
       delay 2
       
       -- Fix:
       -- Change the format back to 'automatic' so that the hard 'date and time' sub-format doesn't persist.
       set format of column "A" to automatic
       -- Now change the format to 'text'.
       set format of column "A" to text
       -- Edit the time representations from the cells's text values.
       repeat with i from 2 to 22
           tell cell i of column "A" to set its value to text 1 thru 10 of (get its value)
       end repeat
       -- Reset the cell format to 'date and time'.
       set format of range "A2:A22" to date and time
   end tell
end tell

Edit: It seems I got a little confused last night. The cell formats don't have to be set to 'date and time' first for yyyy/m/d entries to be recognised as dates. But they do have to be so preset for the entries to be automatically reformatted to the default 'date and time' style.

Here's an alternative demo for when yyyy/m/d text is entered into cells with unmodified 'automatic' formats:

Applescript:


tell application "Numbers"
   activate
   set newDoc to (make new document)
   tell table 1 of sheet 1 of newDoc
       -- Unless the default "Blank" template's been changed, the cell formats are all 'automatic' at this point.
       -- Enter dates in yyyy/m/d format.
       repeat with i from 2 to 22
           set value of cell i of column "A" to "2019/4/" & i
       end repeat
       -- Delay a few seconds to see the result.
       -- On my machine, the dates are shown in the original yyyy/m/d format.
       -- The cell formats are still 'automatic', but have been modified to display dates in the above style.
       -- The cell values are AS dates.
       delay 10
       
       -- Fix:
       -- Change the format to 'text'.
       set format of column "A" to text
       -- Edit the date representations to the required style.
       repeat with i from 2 to 22
           set cellValue to value of cell i of column "A"
           set value of cell i of column "A" to my restyle(cellValue)
       end repeat
       -- Now change the cell format to 'date and time'.
       set format of range "A2:A22" to date and time
   end tell
end tell

-- Restyle a short-date string from yyyy/(m)m/(d)d to dd/mm/yyyy.
-- The original may or may not be padded with leading zeros and may or may not be followed by a time in either 24-hour or 12-hour format.
-- The output string *is* padded with leading zeros where appropriate.
-- This is a separate handler because it uses TIDs and trying to get a string's 'text items' errors inside a Numbers 'tell' statement.
on restyle(oldValue)
   set astid to AppleScript's text item delimiters
   set AppleScript's text item delimiters to {"/", space}
   set {y, m, d} to oldValue's text items
   tell (y * 10000 + m * 100 + d) as text to set newValue to {get text 7 thru 8, get text 5 thru 6, get text 1 thru 4} as text
   set AppleScript's text item delimiters to astid
   
   return newValue
end restyle

Last edited by Nigel Garvey (2019-04-15 04:01:05 am)


NG

Offline

 

#4 2019-04-15 05:18:26 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4879

Re: Formatting date without time in Numbers

To attempt a summary of my previous post:

When scripting Numbers 6.0 (Edit: Things aren't quite the same with Numbers 2.3. If they're different with other versions too, this may not be of much help to vw_as.  hmm ):

• If the format of an empty cell with an unmodified 'automatic' format is changed to 'date and time', the date display style adopted for the cell is a default influenced by the user's Language & Region preferences and includes the hours and minutes.
• If the value of an empty cell with an unmodified 'automatic' format is set to an AS date, the cell's 'automatic' format is modified to display dates in the default style. The format itself isn't changed to 'date and time'. (See the "Cell" tab in Numbers's "Format" pane.)
• If the value of an empty cell with an unmodified 'automatic' format is set to a text value which Numbers can interpret as representing a date, the cell's 'automatic' format is modified to display dates in the same style as the entered text. Getting the cell's 'value' returns an AS date.
• Once a cell's 'automatic' format has been modified to display dates in a certain way, all subsequent dates and date strings entered into the cell are displayed in the same way. However, this can be overridden by setting the cell's format to 'text', entering a new value, and then (if required) changing the format back to 'automatic'.
• If the format of a cell with a date-modified 'automatic' format is explicitly changed to 'date and time', dates and date strings are displayed in the cell in the same way as they were under the date-modified 'automatic' format. To override this, it's necessary first to change the cell's format to 'automatic', then change it to 'text', enter a new value in the required form, and then (if required) change the format back to either 'date and time' or 'automatic'.

Last edited by Nigel Garvey (2019-04-15 06:10:29 am)


NG

Offline

 

#5 2019-04-16 06:11:37 am

KniazidisR
Member
Registered: 2019-03-03
Posts: 144

Re: Formatting date without time in Numbers

For the first time I tried the Numbers application. My God, how miserable it is !!! The cell does not even have a normal Format menu.What does a cell contain?... In what form, etc. ... All this is absent. Format menu on the right mouse click absent too... LibreOffice has everything that a sophisticated user needs, even AppleScript is not needed, thanks to its Visual Basic for APIs interface. In LibreOffice, I achieved even better automation results than in Microsoft Excel.

Simplified version of the scripts above works too:

Applescript:



tell application "Numbers" to tell table 1 of sheet 1 of (open (choose file of type {"com.apple.iwork.numbers.sffnumbers"}))
       set format of range "A3:A3" to automatic
       set format of range "A3:A3" to text
       tell cell 3 of column "A" to set its value to text 1 thru 10 of (get its value)
       set format of range "A3:A3" to date and time
end tell

You can achieve this behavior in "Numbers" by this steps:

1) Go in the menu bar View--->Inspector--->Format
2) In Format menu go --->Cell--->Data Format--->choose Date & Time
3) in Date & Time menu you must choose then for Time value None

Such a long path for such a simple task ... Numbers!!! Apple!!!

Last edited by KniazidisR (2019-04-16 08:24:22 am)


macOS Mojave -- version 10.14.4
Safari -- version 12.1

Offline

 

#6 2019-04-16 07:43:04 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4879

Re: Formatting date without time in Numbers

KniazidisR wrote:

For the first time I tried the Numbers application. My God, how miserable it is !!!


Wait till you try Pages!  wink


NG

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)