Time and Date Selector

[trevolly]

Hi all.

Is there any way to have a time selector and a date selector in a cell in Excel without using add ins? My company won’t allow add ins to the system.

I’ve got a form where an engineer has to select a date and time for a high powered engine run to commence and for some reason they seem to struggle to enter the data correctly. I’ve done a data validation list for dates but times is an issue as it could be a request for any minute of the day. 00:00, 00:01, 00:02 etc.

I was hoping that if you clicked on the cell it would offer a select the time or date system

Thank you all.

 

[mumps]

You can use the DateTimePicker in Excel to insert two Date Picker boxes and then format one of them as a Time Picker.

 

[awoohaw]

You can use the DateTimePicker in Excel to insert two Date Picker boxes and then format one of them as a Time Picker.

@mumps I'm interested in this, too. I have 365. I cannot find DateTimePicker using search, or in the "All Commands" list of update Quick Access Tool Bar. Please show how to start this.

 

[mumps]

I'm not familiar with 365 but do the following:

1. If the "Developer" is not visible in the Quick Access Tool Bar, I add it.
2. In that tab, click 'Insert' and look for the Microsoft Date and Time Picker Control.
3. If it not visible, click on the button at the bottom right (it has a screwdriver and wrench icons)
4. This brings up a list of additional controls.
5. Scroll down to select the Microsoft Date and Time Picker Control.
6. Click on your sheet and the control will be inserted.
7. While it is still selected, copy/paste it to insert another control.
8. Right click the second control and click Properties.
9. In the pop up, click Format and then select 'Time'.
10. In the Value property, remove the date to leave only the time.

Put the following code in the worksheet sheet code module and change the range (in red) to suit your needs.

Private Sub DTPicker1_Change()
     Range("A1") = DTPicker1.Value
End Sub

Private Sub DTPicker2_Change()
     Range("B1") = DTPicker2.Value
End Sub

I hope this works in 365. If the Microsoft Date and Time Picker Control is not available in your version of Excel, you may have to do some research to see if it can be added.

 

[awoohaw]

I'm not familiar with 365 but do the following:

1. If the "Developer" is not visible in the Quick Access Tool Bar, I add it.
2. In that tab, click 'Insert' and look for the Microsoft Date and Time Picker Control.
3. If it not visible, click on the button at the bottom right (it has a screwdriver and wrench icons)
4. This brings up a list of additional controls.
5. Scroll down to select the Microsoft Date and Time Picker Control.
6. Click on your sheet and the control will be inserted.
7. While it is still selected, copy/paste it to insert another control.
8. Right click the second control and click Properties.
9. In the pop up, click Format and then select 'Time'.
10. In the Value property, remove the date to leave only the time.

Put the following code in the worksheet sheet code module and change the range (in red) to suit your needs.

Private Sub DTPicker1_Change()
     Range("A1") = DTPicker1.Value
End Sub

Private Sub DTPicker2_Change()
     Range("B1") = DTPicker2.Value
End Sub

I hope this works in 365. If the Microsoft Date and Time Picker Control is not available in your version of Excel, you may have to do some research to see if it can be added.

Thanks, in the list of controls to insert there are Microsoft Outlook Date Control and Microsoft Outlook Time Control, not Microsoft Date and Time Picker Control.

These do not work in 365 Excel, as their names imply, they're used in Outlook. VBA has not changed in a long time. I use desktop excel 365.

If there is an excel date/time picker, my guess is that it is a custom control you imported as some time. If not please show in an image how to find that picker.
Or does there need to be a new reference library selected from the VBA Tools References menu? These are the only reference libraries I have selected, please compare to yours, and advise:

I just found this how to link. It requires downloading some plug ins. It may not work for OP:

Redirecting

Sorry it says "redirecting" but the text of the link is this:
//answers.microsoft.com/en-us/windows/forum/all/how-to-download-microsoft-date-and-time-picker/41ad03ac-ad00-4d81-b696-169adebade06
This goes back to Windows 10 and Office 2010 and 2013, im unsure how it behave in later versions.

 

[mumps]

To be honest, I can't remember if I imported that control. I do have a reference library named "Microsoft Windows Common Controls-2 6.0 (SP6) selected in the VBA Tools References menu. Check to see if you have that reference library.

 

[awoohaw]

I do not have the reference library you mention.

It looks like the Date and Time picker is not in 365 and only available via add ins:

 

[mumps]

That's too bad. Microsoft updates Excel and leaves out an important feature. If you want a third party date picker, please let me know and I will attach one.

 

[Fluff]

The date time picker is available in 365, but probably only in the 32bit version.

 

[mumps]

@Fluff
Good to know. Many thanks.