I'm trying to use the Date Time Picker control as a time picker and only want hours and minutes to show. Can this be done with its custom format property (Example)? The usual number formatting string doesn't seem to work.
View 6 RepliesI have a file that has the Date and Time combined into one cell. I want to separate the two, and cannot find anywhere on the net to do so!
This is the cells format:
d/mm/yyyy h:mm
Cells look like this:
28/05/2008 12:30
I'm building a userform excel application and am using the MS date/time picker control. I've run into the familiar "computer doesn't have OCX" problem and want to avoid using a custom OCX.
I was thinking of just making my own control instead. Has anyone made one in VBA? I found some VB examples which I could port over, but they use a lot of api calls. For reliability, I'd like to keep this as simple as possible though.
I have a lot of data in the following format: 20080531T085930. Formatting the cells with yyyymmddThhmmss doesn't seem to be working. What's the correct way to handle this?
View 3 Replies View RelatedI have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.
I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).
When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)
So my questions are:
1. Is this due to the formatting?
2. Is there a way to change the date format supplied by the date picker?
On the attachment i have a userform which pops up when cell B2 is selected. It is a month view from the additional controls on the control toolbox. I've also added a couple of text boxes with spin buttons.
A few amendments i want to make to it are:
User form pops up next cell B2
'Enter button' enters date and time into cell B2
Spin buttons increase/decrease hour field in incremnts of 1 and minutes field in increments of 5
I'm trying to use the Date/Time ActiveX control in Excel 2000 and it is giving me problems. I am attempting to use this control contained directly on a worksheet (not contained on a separate form). My problem is that each time I start the workbook, the date/time control resizes itself to have an extremely large height. I set the control's height at 18 but upon loading it shows the height as about 5x that value. However, the properties of the control still show it listed with a height of 18? No matter what I do, I cannot seem to find a way to have the control maintain its initial height. I've tried this in several different workbooks, and it does the same everytime.
View 9 Replies View RelatedHow should i reformat the date and time picker? at this moments, it shows the date in the format of mm/dd/yyyy but the format that i want is dd/mm/yyyy.
View 10 Replies View RelatedI am trying to use the Date and Time Picker 6.0 and I have add it to the userform and named it DateofContact.
When I play the userform I get a error stating:
"Compile Error-Method or Data member not found"
Below I have added the code and it shows in red where the problem is.
Private Sub UserForm_Activate()
PSA.Text = Cells(lCurrentRow, 2).Value
cboTypeofContact.Text = Cells(lCurrentRow, 3).Value
DateofContact.Text = Cells(lCurrentRow, 4).Value
TimeofContact.Text = Cells(lCurrentRow, 5).Value
End Sub
I am not sure what to put in its place.
I recently reformatted my hard drive and reinstalled my software. Now I don't seem to have the Date and Time Picker Control (among others) that I used to.
I did a thourough install of Microsoft Office 2000 Professional, and switched from Windows 98SE to Windows 2000 Professional. Isn't that control standard in such an installation? If its not, where can I get it?
i have just discovered the Microsoft date and time picker. at the moment i have a text box on my userform into which i put the date.can i use code to transfer the date to the text box or do i use the date picker instead of the textbox. if i used the date picker what is the code for the date picker
View 2 Replies View RelatedI have some excel tools developed in Excel 2003 which uses the date and time picker control (DtPicker). When I open my files in Excel 2007 the DtPicker is missing and the change_event will not load the calendar. I cannot find and activate the datepicker in the Reference base.
View 6 Replies View RelatedI just installed Office 2013 Pro. In my Office 2010 Additional Controls toolbox, the control toolbox has a Date and Time Picker(DTPicker1) . I cannot find it in the Additional Controls toolbox in Excel 2013. I certainly hope they did not choose to not make it available in Excel 2013 - this is an extremely important control for my Excel spreadsheets as it allows inputting a date range on a userform with DTPicker1 and DTPicker2 and generating reports using VBA in conjunction with Autofiltering code on a Generate Report button.
View 8 Replies View RelatedI inserted the Date & Time picker Control into my worksheet. I have it linked to a cell. When I change the date, the new date goes into the cell, but it does not appear to be a "date" . The date formulas that use that cell do not evaluate properly. It looks different than when I type a date directly in to the cell (it is left justified). I tried formatting the cell as a date and the format property of the control is set to shortDate.
View 2 Replies View RelatedI want a drop down calender to choose date from and found that i can use More Controls> "microsoft Date and Time Picker 6.0 (SP4)" for excel 2007 but it doesn't work with other excel and shows a "x" not recognised in some other machines.
View 1 Replies View RelatedI am using the Microsoft Date and Time Picker Control 6.0 (SP4) from the more controls option on the developer Ribbon. The issue I have is when I protect the sheet I lose the functionality?
I have ranges selected as editable by user when the protect is on - this includes the linked cell to the date picker but it still doesn't work.
I have just added two date and time picker controls to my spread sheet to allow the user to select the date range.
They are linked to cells C9 & C10.
The problem is that the formula I using to check whether the first selection pre-dates the second has stopped calculating correctly when using the date and time pricker controls.
The formula is:
=IF(C9>C10,"Error","OK")
I have checked it on the same sheet by just typing the dates in and it works fine...I have tried formatting cells C9 & C10 to short date, general, text, number but it still does not calculate correctly.
I am trying to add a calendar picker to Excel 2010. Everything I read online says that it is possible, you just need to use the ActiveX control "Microsoft Date and Time Picker Control 6.0", and that it should be listed under Developer Tab -> Insert -> More Controls ->... But it's not listed.
I also tried downloading the Microsoft Windows Common Controls-2 6.0, but when I tried to add it to Excel using "Register Custom...", it says "Cannot register this control".
Why this control is missing? and I can't add it?
I was trying to use this guide to add a date picker in:
[URL]
and noticed that the date picker does not appear in my active x controls list.
I am definitely on Excel 2010 so it should be there right?
I have a column in excel which gets populated in the format "2/1/1980" when copied from a different source. I want a macro to run on this column and convert this in the below format. Required format: '02-01-80 (note the ' symbol before 02 which will keep it as text)
Existing ----> Required
2/1/1980 --> '02-01-80
2/1/1970 --> '02-01-70
2/1/1983 --> '02-01-83
I need this macro to run on two columns and replace them with the required format. I can do a find and replace either in the sheet or through hard coding the VBA, but as the date format keeps growing, I would need to do this via code and hence requesting the same.
How do you make custom date format to a cell in such a way that it appears e.g. "Jan-06"?
I tried custom format "Mmm-yy" but it still resulted to "jan-06".
I want the "J" here to be in the upper case.
I have not learned the custom number format feature. If someone could give me the format for the cell to display:
4/23 Wed
I have loaded a .csv file in which the first column contains date/times, e.g. 01/12/2012 00:00. How do I now tell Excel (2010) that this is in fact a date/time format? If I select one or more of the cells, click on the Number dialog box launcher and try to pick a suitable format tghe cells resolutely refuse to budge from being text (i.e. left-justified, still allows me to edit the 'seconds' component to a number > 60). Also which data type should I be using? The only one that appears to have a full date/time format listed is Custom (not Date or Time).
View 4 Replies View RelatedI want to display 2 dates in one cell and have them custom formatted.
Eg. Display in one cell: 26 Jun - 27 Jun
With text typed: 26-6 - 27-6
(Or something along the lines of)
I know that dd mmm will display one date. But I cannot get 2 dates formatted. I tried dd mmm; dd mmm" - "dd mmm, which didn't work.
i have the required date as part of a string, which i need to pull out and then format it as "yyyy-mm-dd hh:mm:ss" and put it in another file which will be saved as .csv
Here is the code i am trying to modify without much success so far
Sub dateFormat()
Dim dtMydate As Date
Dim sMydate As String
Dim strWhen As String
strWhen = "As on 13-MAR-2007 14:45:59 Hours IST" ' the starting string
'strWhen = Cells. Find("As on")
'take out the date portion from strWhen
strWhen = Mid(strWhen, InStr(strWhen, "-") - 2, (InStr(strWhen, "Hours") - 1) - (InStr(strWhen, "-") - 2))
dtMydate = CDate(strWhen)
Debug.Print dtMydate 'returned value is 13/03/2007 2:45:59 PM
dtMydate = Format(dtMydate, "yyyy-mm-dd hh:mm:ss")
Debug.Print dtMydate 'returned value is again 13/03/2007 2:45:59 PM...
I am needing to create a sequential serial number using a format yyyy-nnn, where yyyy is the current year and nnn is a sequential number (2007-001). I've tried using both a formula and custom formats but cannot get the cell contents to display as desired. I've used TODAY()&"-"&"001", which gives a valid result of 39100-001, but this not what I need. I've tried many other combinations in the custom option for formatting the cell.
View 5 Replies View RelatedI am trying to get a custom date format that looks like:
30MAY07
I can get 30May07 with ddmmmyy but can't seem to find how to get the 3 letter month to display in caps.
Excel 2002 SP3 on WinXP
I'm a fan of the Excel 2010 table styles, but can't figure one thing out. When I create a table in excel (Ctrl+T), I like to reformat it with a new defined style. When I save the style, I can use it while I'm in that instance of excel, but when I close and reopen excel, the style is gone. How to save the custom formats to they are always available?
View 3 Replies View RelatedI`m building a macro for which I would like to save a parameter for the macro to read and set.
I would like this parameter to be saved (permanently) in something like a custom workbook property, i.e. Tarabo (=Yes/No).
How can I create/read/wright using vba code?
I have one column with as many as 50,000 or more rows. The data format for each row/ cell is unique as shown below ( date and time). I wanted to split the data as shown in "Formatted Data" below. Have Tried Text To Column formatting but didn't work right.
Raw Data: Formatted Data (2 cells):
2005/11/02 23:55:15.758 ==> 2005/11/02 23:55:15.758
2005/11/02 23:58:16.698 ==> 2005/11/02 23:58:16.698
2005/11/03 00:07:13.830
2005/11/03 00:10:14.971