You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How to insert a Date dropdown picker in Numbers?

Hi! I'm familiar with inserting sliders, steppers and pop-up menus in Numbers but I was wondering if there was a way to create a dropdown date picker?


The only way I see to do it so far is by using a pop-up menu and writing on a separate table the days of the year, but this feels a bit cumbersome and given that the pop-up menu only takes 250 items, it wouldn't take a whole year. I want a simple date picker when clicking on a cell.


Has anybody done this before? I looked but could not find info on this.


Thanks!

MacBook Pro 13″, macOS 11.4

Posted on Jul 8, 2021 9:46 AM

Reply
Question marked as Top-ranking reply

Posted on Jul 8, 2021 2:46 PM

You can do it with AppleScript, invoked with a keyboard combination. I found a few datepicker scripts online. They were all basically the same script. I barely know how they work but I adapted one to insert the picked date & time into Numbers cells. The one problem I found with this datepicker is it does not highlight the chosen date when you pick it. I was unable to figure out how to change that. It will correctly return the date you clicked on but there is no visual feedback of the date you picked. You'll see what I mean when you try it out.


Any computer that will be running your Numbers document would need this quick action installed to use the date picker; it is not part of the document.


Try it out first before doing the other stuff below:

  1. Open Script Editor app
  2. Paste the code from below into a new document
  3. Select a cell or a range of cells in Numbers
  4. Back in Script Editor hit the run button


If it looks like it does what you want, continue on.


From what I can tell, this datepicker script has to be run from within the Numbers app if you want its window to be on top. So, I made a "quick action" from it that can be run from the Numbers/Services menu.


  1. Open the Automator app
  2. Create a new Quick Action
  3. Set the workflow to receive "no input" in "Numbers.app"
  4. From the Utilities folder, drag the "Run Applescript" action to the window
  5. Delete everything that is there and replace it with the script from below
  6. Save it with a name like Numbers Date Picker


In Numbers, this 'quick action" will show up in the Numbers/Services menu.

Select a cell or range of cells then run the date picker from the Services menu


If you are okay with how it works and you want to be able to invoke it with a keyboard shortcut,

  1. Open System Preferences/Keyboard/Shortcuts/App Shortcuts
  2. Hit the + and create a shortcut for Numbers
  3. Type in the menu item name exactly as you see it in the menu, including all caps and punctuation
  4. Give it a keyboard shortcut


Note: With one line change, the date picker can be shown as a scroller instead of a "calendar" where you can pick the day, month, year, etc. number by number. This solves the problem of lack of visual feedback but it is not the calendar style picker most people think of.




# 06/04/16 09:35:02
# Author: Shane Stanley
# Adapted by Christopher Stone
# Fixed & Rewritten by CJK
# Adapted for Numbers app by Badunit
--------------------------------------------------------------------------------
use framework "AppKit"
use scripting additions

property this : a reference to the current application
property nil : a reference to missing value
property _1 : a reference to reference

property NSAlert : a reference to NSAlert of this
property NSDatePicker : a reference to NSDatePicker of this
property NSView : a reference to NSView of this

property NSAlertFirstButtonReturn : 1000
property NSAlertSecondButtonReturn : 1001
property NSHourMinuteSecondDatePickerElementFlag : 14
property NSClockAndCalendarDatePickerStyle : 1
property NSYearMonthDayDatePickerElementFlag : 224

--------------------------------------------------------------------------------
property date : missing value
property returnCode : missing value
--------------------------------------------------------------------------------
on run
	its performSelectorOnMainThread:("showDatePicker:") withObject:{¬
		NSClockAndCalendarDatePickerStyle, ¬
		NSYearMonthDayDatePickerElementFlag + ¬
		NSHourMinuteSecondDatePickerElementFlag} ¬
		waitUntilDone:true
	
	if returnCode = (NSAlertSecondButtonReturn) then error number -128
	
	tell application "Numbers" to tell the front document to tell active sheet to tell (first table whose class of selection range is range)
		repeat with c in (get selection range)'s cells
			set value of c to my date
		end repeat
	end tell
end run

on showDatePicker:params
	local params
	
	set {PickerStyle, PickerElements} to params
	
	tell NSDatePicker's alloc()
		initWithFrame_({{0, 0}, {100, 100}})
		setDatePickerStyle_(PickerStyle)
		setDatePickerElements_(PickerElements)
		setDateValue_(current date)
		set fittingSize to fittingSize()
		setFrameSize_(fittingSize)
		
		set View to NSView's alloc()
		View's initWithFrame:{{0, 0}, {100, 175}}
		View's setFrameSize:fittingSize
		View's addSubview:it
		
		tell NSAlert's alloc()
			init()
			setMessageText_("Pick a date and time")
			setInformativeText_("Any date")
			addButtonWithTitle_("OK")
			addButtonWithTitle_("Cancel")
			setAccessoryView_(View)
			set returnCode to runModal()
		end tell
		
		set my date to dateValue() as date
	end tell
end showDatePicker:
---------------------------------------------------------------------------❮END❯


11 replies
Question marked as Top-ranking reply

Jul 8, 2021 2:46 PM in response to QU3ST10N

You can do it with AppleScript, invoked with a keyboard combination. I found a few datepicker scripts online. They were all basically the same script. I barely know how they work but I adapted one to insert the picked date & time into Numbers cells. The one problem I found with this datepicker is it does not highlight the chosen date when you pick it. I was unable to figure out how to change that. It will correctly return the date you clicked on but there is no visual feedback of the date you picked. You'll see what I mean when you try it out.


Any computer that will be running your Numbers document would need this quick action installed to use the date picker; it is not part of the document.


Try it out first before doing the other stuff below:

  1. Open Script Editor app
  2. Paste the code from below into a new document
  3. Select a cell or a range of cells in Numbers
  4. Back in Script Editor hit the run button


If it looks like it does what you want, continue on.


From what I can tell, this datepicker script has to be run from within the Numbers app if you want its window to be on top. So, I made a "quick action" from it that can be run from the Numbers/Services menu.


  1. Open the Automator app
  2. Create a new Quick Action
  3. Set the workflow to receive "no input" in "Numbers.app"
  4. From the Utilities folder, drag the "Run Applescript" action to the window
  5. Delete everything that is there and replace it with the script from below
  6. Save it with a name like Numbers Date Picker


In Numbers, this 'quick action" will show up in the Numbers/Services menu.

Select a cell or range of cells then run the date picker from the Services menu


If you are okay with how it works and you want to be able to invoke it with a keyboard shortcut,

  1. Open System Preferences/Keyboard/Shortcuts/App Shortcuts
  2. Hit the + and create a shortcut for Numbers
  3. Type in the menu item name exactly as you see it in the menu, including all caps and punctuation
  4. Give it a keyboard shortcut


Note: With one line change, the date picker can be shown as a scroller instead of a "calendar" where you can pick the day, month, year, etc. number by number. This solves the problem of lack of visual feedback but it is not the calendar style picker most people think of.




# 06/04/16 09:35:02
# Author: Shane Stanley
# Adapted by Christopher Stone
# Fixed & Rewritten by CJK
# Adapted for Numbers app by Badunit
--------------------------------------------------------------------------------
use framework "AppKit"
use scripting additions

property this : a reference to the current application
property nil : a reference to missing value
property _1 : a reference to reference

property NSAlert : a reference to NSAlert of this
property NSDatePicker : a reference to NSDatePicker of this
property NSView : a reference to NSView of this

property NSAlertFirstButtonReturn : 1000
property NSAlertSecondButtonReturn : 1001
property NSHourMinuteSecondDatePickerElementFlag : 14
property NSClockAndCalendarDatePickerStyle : 1
property NSYearMonthDayDatePickerElementFlag : 224

--------------------------------------------------------------------------------
property date : missing value
property returnCode : missing value
--------------------------------------------------------------------------------
on run
	its performSelectorOnMainThread:("showDatePicker:") withObject:{¬
		NSClockAndCalendarDatePickerStyle, ¬
		NSYearMonthDayDatePickerElementFlag + ¬
		NSHourMinuteSecondDatePickerElementFlag} ¬
		waitUntilDone:true
	
	if returnCode = (NSAlertSecondButtonReturn) then error number -128
	
	tell application "Numbers" to tell the front document to tell active sheet to tell (first table whose class of selection range is range)
		repeat with c in (get selection range)'s cells
			set value of c to my date
		end repeat
	end tell
end run

on showDatePicker:params
	local params
	
	set {PickerStyle, PickerElements} to params
	
	tell NSDatePicker's alloc()
		initWithFrame_({{0, 0}, {100, 100}})
		setDatePickerStyle_(PickerStyle)
		setDatePickerElements_(PickerElements)
		setDateValue_(current date)
		set fittingSize to fittingSize()
		setFrameSize_(fittingSize)
		
		set View to NSView's alloc()
		View's initWithFrame:{{0, 0}, {100, 175}}
		View's setFrameSize:fittingSize
		View's addSubview:it
		
		tell NSAlert's alloc()
			init()
			setMessageText_("Pick a date and time")
			setInformativeText_("Any date")
			addButtonWithTitle_("OK")
			addButtonWithTitle_("Cancel")
			setAccessoryView_(View)
			set returnCode to runModal()
		end tell
		
		set my date to dateValue() as date
	end tell
end showDatePicker:
---------------------------------------------------------------------------❮END❯


Jul 9, 2021 4:32 AM in response to Badunit

Hi Badunit, it worked nicely thanks. At least now if I double click on the cell from a mobile, anybody can choose 'today' to log the date easier.


How could I set it to be shown as a scroller? I thought it'd be as easy as changing the code line


property NSClockAndCalendarDatePickerStyle : 1


from 1 to 0 (https://www.monkeybreadsoftware.net/class-nsdatepickermbs.shtml) but the script stopped running.


Thanks !

Jul 9, 2021 5:41 AM in response to QU3ST10N

Nice reference. I've bookmarked it. Maybe something in there can point to how to get the selected date to highlight on the calendar.


I have posted a different version of the script below. It is based on an earlier version of the datepicker script before someone "cleaned it up", which actually made it harder to understand if you ask me. Paste it in place of the script provided before. This one is a lot clearer how to make the change you want. Find the line that sets the style to NSClockAndCalendarDatePickerStyle and put in NSTextFieldAndStepperDatePickerStyle instead. That's all there is to it. It is actually a stepper, not a "scroller". My mis-wording. You can step through or type in the numbers.


The whole thing is probably clunkier than it should be but I don't know AppleScript well enough to do anything about it.


-- Author: Shane Stanley
-- Edited by Badunit for use within the Numbers app
-- July 9, 2021

use scripting additions
use framework "AppKit"

property thedate : missing value
property returncode : missing value

on run
	its performSelectorOnMainThread:("showDatePicker:") withObject:{} waitUntilDone:true
	
	if returncode = (current application's NSAlertSecondButtonReturn) then error number -128
	
	tell application "Numbers" to tell the front document to tell active sheet to tell (first table whose class of selection range is range)
		repeat with c in (get selection range)'s cells
			set value of c to thedate
		end repeat
	end tell
end run

on showDatePicker:params
	local params
	
	set datePicker to current application's NSDatePicker's alloc()'s initWithFrame:(current application's NSMakeRect(0, 0, 100, 100))
	
	-- set datepicker style: choices are 
	-- NSTextFieldAndStepperDatePickerStyle 
	-- NSClockAndCalendarDatePickerStyle
	-- NSTextFieldDatePickerStyle
	
	datePicker's setDatePickerStyle:(current application's NSClockAndCalendarDatePickerStyle)
	
	-- set datepicker elements: choices include 
	-- NSHourMinuteDatePickerElementFlag
	-- NSHourMinuteSecondDatePickerElementFlag
	-- NSTimeZoneDatePickerElementFlag
	-- NSYearMonthDatePickerElementFlag
	-- NSEraDatePickerElementFlag
	
	datePicker's setDatePickerElements:((current application's NSYearMonthDayDatePickerElementFlag) + (current application's NSHourMinuteSecondDatePickerElementFlag as integer))
	
	-- set datepicker initial date
	
	datePicker's setDateValue:(current application's NSDate's |date|())
	
	-- set datepicker size
	
	set theSize to datePicker's fittingSize()
	datePicker's setFrameSize:theSize
	
	-- set the view
	
	set theView to current application's NSView's alloc()'s initWithFrame:(current application's NSMakeRect(0, 0, 100, 200))
	theView's setFrameSize:theSize
	
	-- add the picker to the view
	
	theView's setSubviews:{datePicker}
	
	-- create an alert
	
	set theAlert to current application's NSAlert's alloc()'s init()
	
	-- set up alert
	
	tell theAlert
		its setMessageText:"Pick a date"
		its setInformativeText:""
		its addButtonWithTitle:"OK"
		its addButtonWithTitle:"Cancel"
		its setAccessoryView:theView
	end tell
	
	-- show alert in modal loop
	
	set returncode to theAlert's runModal()
	
	-- retrieve date
	
	set thedate to datePicker's dateValue() as date
	
end showDatePicker:


Jul 9, 2021 5:30 PM in response to QU3ST10N

I'm still playing around with this script to make it better fit my needs. The version in the link below gives the user the choice of returning date only (time = 12:00:00 AM) or the date & time. If you want the stepper style picker, open it in Automator and make that change to the one line.


https://www.dropbox.com/sh/iaf4t771zulud4v/AABQZlw_9kO3FOk6PsXNbawBa?dl=0


Services (quick actions) are stored in the username/Library/Services folder.

How to insert a Date dropdown picker in Numbers?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.