want to set the date format of a cell in number app to dd-mmm-yy through apple script

@YellowBox and @SGIII have the right idea - you can apply a format to a range of cells in one go.

More to the point, this is actually something that AppleScript can't do (at least directly).

Numbers' AppleScript dictionary allows a script to set a cell's format, but only to one of the built-in standards (e.g 'Date and Time', 'Number', 'Currency', etc.), but strangely enough, there is no support for variations within those standard types, nor custom types.

By that, I mean you can define a cell format as 'date and time' but you can't define the specific HH-MMM-YY format of how that date should be presented. Similarly you can say a cell is 'currency', but you can't define the currency setting (US Dollar, Euro, Yen, Polish Zloty, etc.), nor can you set decimal places.

So no matter what, this can't be done via AppleScript (other than via UI scripting). Therefore you're going to have to jump through some hoops either way - either building the format in the way you want, or applying the cell formatting directly.

dd-Mmm-yy is a custom format, not one of the built-in date formats. The problem I am running into is the inability to create a custom format using AppleScript. I don't see how to create one or apply one I made in Numbers or to even to copy one from one cell to another.

If you could accept one of the built-in date formats instead of dd-MMM-yy, you can write the value to the cell as a string and it will get formatted as a date using that built-in format (if the cell's format is automatic). If the cell has something else in it already I belive you will need to set the value to "", set the format to automatic, then write the new value.

If the "date" values you have now are strings, not actual dates, this would get you part way toward the conversion. Then all you would need to do is apply your pre-made custom date format to the column of cells.

Simple example:

tell application "Numbers"
	tell front document
		tell table 1 of sheet 1
			set dateValue to value of cell "A2"
			set value of cell "A7" to my formatDate(dateValue)
		end tell
	end tell
end tell

on formatDate(dateValue)
	if dateValue is not missing value then
		set dd to text -1 thru -2 of ("0" & day of dateValue as string)
		set mmm to text 1 thru 3 of (month of dateValue as string)
		set yy to text -1 thru -2 of (year of dateValue as string)
		return dd & "-" & mmm & "-" & yy
	else
		error "invalid date in Numbers cell"
	end if
end formatDate

Result:

SG

MukundMandke wrote:

Wanted an apple script solution..

You don't explain why. In any case, you may have to keep wanting. There is no (easy) way to set the specific date format of Date & Time value with AppleScript.

There are ways to work around that, but would need a LOT more specifics (with screenshots) on what you are doing and why you need a script rather than using the existing app interface designed for that purpose.

SG

Hi MukundMandke,

Apple Script seems to be overkill. We can "pull" the Day, Month and Year from that cell using formulas in Numbers and then create the Date that you want (also using a Numbers formula).

Is the date imported from elsewhere?

If you are seeing DD-mm-yyyy:hh-mm-ss.nnn am/pm in a Numbers cell, then it is not a valid Numbers Date & Time. It is formatted as either Automatic or Text, not Date & Time.

Please post a screen shot with the cell selected and also the Format Panel (on the right ) > Cell tab > Data Format, such as this:

Regards,

Ian.

MukundMandke wrote:

I am copying few hundreds of transactions from one sheet of numbers doc to another sheet for generating a report. While doing that I clear the data and copy it. While doing this the date format comes as you have shown - DD/mm/yy hh:mm:as AM/PM.

You can try this:

1. Format the dates as you want them to appear in the "report" table.
2. Clear the data by selecting the cells and hitting the delete key.
3. Select cells in the source table and command-c to copy the data.
4. Select cells in the "report" table where you want the data to go.
5. Edit > Paste and Match Style (or option-shift-command-v).

SG

You are taking advantage of the ability of Numbers to recognize a formatted string and accept it as Date & Time.

You could consider shortening your handler (as functions are called in AppleScript) to something like this;

on formatDate(dateValue)
	if dateValue is not missing value then
		set dd to text -1 thru -2 of ("0" & day of dateValue as string)
		set mmm to text 1 thru 3 of (month of dateValue as string)
		set yy to text -1 thru -2 of (year of dateValue as string)
		return dd & "-" & mmm & "-" & yy
	else
		error "invalid date in Numbers cell"
	end if
end formatDate

You can make the error trapping more or less elaborate as needed for your situation. This just catches a blank cell where a date is expected.

SG