Free Microsoft Excel 2013 Quick Reference

iSamRad Excel Date Picker (v3.0)

The iSamRad.com date picker has been updated to v3.0 ... well it was
about a month ago but didn't get to posting it till now. It's a free
xla download, so have fun with it!

http://www.isamrad.com/excelcal


Post your answer or comment

comments powered by Disqus
Hi,

I am creating a worksheet templates for people to record their work. I would like to create a date picker where the calendar would only appear when you click on a particular cell and once you have selected your date, the calendar disappears and the date appears in the cell.....how can I do this??

Thanks,

Wshadow

I have a Userform that I created in Excel 2007 with a Calendar. When I open this in Excel 2010, the Calendar isn't part of that version, so the object is removed from the Userform and I am forced to comment out the calendar script.

I have heard about the Date Picker, but I have no idea how to use it.

Is there a tool I can use to allow the Calendar to come up when the Userform is opened with Excel 2007 and the Date Picker when the program is opened using Excel 2010?

This calendar allows the user to select a date and then the information is passed to a cell where the date is filled in.

I need the script to be versatile between the two and I'm racking my brain trying to figure this out.

Any advice or direction is greatly appreciated. I have no idea where to start when trying to combine the two date options.

Here is what I've done so far on my own... My mind is very scrambled... Please help!

Private Sub cboEnrollmentAdvisor_Change()

End Sub

Private Sub cmdClearForm_Click()
    Call UserForm_Initialize
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdOK_Click()
Dim NR As Long

'transfer values to database
    With ActiveWorkbook.Sheets("November2011")
        NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        .Range("B" & NR).Value = txtFName.Value
        .Range("C" & NR).Value = txtLName.Value
        .Range("D" & NR).Value = txtIRN.Value
        .Range("A" & NR).Value = cboEnrollmentAdvisor.Value
        .Range("E" & NR).Value = frmCalendar.Value
    End With

'Reset values
    txtFName.Value = ""
    txtLName.Value = ""
    txtIRN.Value = ""
    cboEnrollmentAdvisor.Value = ""
    frmCalendar.Value = ""
End Sub

Private Sub cmdSubmit_Click()

End Sub



Private Sub TextBox1_Change()

End Sub

Private Sub CommandButton1_Click()
    Unload Me
    ActiveCell.Select
End Sub
Private Sub MonthView1_DateClick(ByVal DateClicked As Date)

    ActiveCell = MonthView1.Value
    Unload Me
    ActiveCell.Select

End Sub
Private Sub UserForm_Activate()
    If Not IsDate(ActiveCell.Value) Then
        Me.MonthView1.Value = Date
    Else
        Me.MonthView1.Value = ActiveCell.Value
    End If
End Sub
Private Sub UserForm_Initialize()
    Dim frm As Long, frmstyle As Long
    If Val(Application.Version) >= 9 Then
        wHandle = FindWindow("ThunderDFrame", Me.Caption)
    Else
        wHandle = FindWindow("ThunderXFrame", Me.Caption)
    End If
    If wHandle = 0 Then Exit Sub
    frm = GetWindowLong(wHandle, GWL_STYLE)
    frm = frm Or &HC00000
    SetWindowLong wHandle, -16, frmstyle
    DrawMenuBar wHandle

    With Me
     .Left = ActiveCell.Offset(0, 1).Left
     .Top = ActiveCell.Top + ActiveCell.Height + 15
     .StartUpPosition = 0
    End With

End Sub



Private Sub Image1_Click()

End Sub

Private Sub Label9_Click()

End Sub

Private Sub Degree_Initialize()
    txtFName.Value = ""
    txtLName.Value = ""
    txtIRN.Value = ""
    With cboEnrollmentAdvisor
        .AddItem "********************************************Undergraduate
Degrees*******************************************"
        .AddItem ""
        .AddItem "Bachelor of Science in Early Childhood Education (Leads to Credential)"
        .AddItem "Bachelor of Science in Elementary Education and Special Education (Dual Major) (Eligible for
Institutional Recommendation)"
        .AddItem "Bachelor of Science in Elementary Education Grades K-8 (Emphasis in Early Childhood Education)
(Eligible for Institutional Recommendation)"
        .AddItem "Bachelor of Science in Elementary Education Grades K-8 (Emphasis in English) (Eligible for
Institutional Recommendation)"
        .AddItem "Bachelor of Science in Elementary Education Grades K-8 (Emphasis in Math) (Eligible for Institutional
Recommendation)"
        .AddItem "Bachelor of Science in Elementary Education Grades K-8 (Emphasis in Science) (Eligible for
Institutional Recommendation)"
        .AddItem "Bachelor of Science in Secondary Education (Emphasis in Business Education) (Eligible for
Institutional Recommendation)"
        .AddItem "Bachelor of Science in Secondary Education (Emphasis in English) (Eligible for Institutional
Recommendation)"
        .AddItem "Bachelor of Science in Secondary Education (Emphasis in Math) (Eligible for Institutional
Recommendation)"
        .AddItem "Bachelor of Science in Secondary Education (Emphasis in Social Studies) (Eligible for Institutional
Recommendation)"
        .AddItem "Bachelor of Science in Secondary Education with an Emphasis in Biology (Eligible for Institutional
Recommendation)"
        .AddItem "Bachelor of Science in Secondary Education with an Emphasis in Chemistry (Eligible for Institutional
Recommendation)"
        .AddItem "Bachelor of Science in Secondary Education with an Emphasis in Physical Education (Eligible for
Institutional Recommendation)"
        .AddItem ""
        .AddItem ""
        .AddItem "********************************************Graduate
Degrees*******************************************"
        .AddItem ""
        .AddItem "Master of Arts in Teaching with an Emphasis in Professional Learning Communities (Not Eligible for
Institutional Recommendation)"
        .AddItem "Master of Arts in Teaching with an Emphasis in Teacher Leadership (Not Eligible for Institutional
Recommendation)"
        .AddItem "Master of Education in Curriculum and Instruction: Reading with an Emphasis in Elementary Education
(Not Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Curriculum and Instruction: Reading with an Emphasis in Secondary Education
(Not Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Curriculum and Instruction: Technology (Not Eligible for Institutional
Recommendation)"
        .AddItem "Master of Education in Early Childhood Education (Leads to Credential)"
        .AddItem "Master of Education in Early Childhood Education (Not Eligible for Institutional
Recommendation)"
        .AddItem "Master of Education in Educational Administration (Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Educational Leadership (Not Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Elementary Education (Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Elementary Education (Not Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Elementary Education: Arizona Teaching Intern Certificate Program (Eligible for
Institutional Recommendation)"
        .AddItem "Master of Education in Secondary Education (Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Secondary Education: Arizona Teaching Intern Certification Program (Eligible
for Institutional Recommendation)"
        .AddItem "Master of Education in Special Education for Certified Special Educators (Not Eligible for
Institutional Recommendation)"
        .AddItem "Master of Education in Special Education: Cross-Categorical (Not Eligible for Institutional
Recommendation)"
        .AddItem "Master of Education in Special Education: Cross-Categorical: Arizona Teaching Intern Certification
Program (Eligible for Institutional Recommendation)"
        .AddItem "Master of Education in Special Education: Cross-Categorical (Eligible for Institutional
Recommendation)"
        .AddItem "Master of Education in Teaching English to Speakers of Other Languages (TESOL) (Not Eligible for
Institutional Recommendation)"
        
    End With
    cboEnrollmentAdvisor.Value = ""
End Sub

Private Sub CommandButton1_Click()

End Sub


Hi

I was using an file in excel 2007 in which the date picker option works fine. But when i upgraded the excel from 2007 to 2010, the date picker option is not working and throwing the below error.

Compile error in hiddenModule : SheetBUT.

Please help in providing an solution asap.

Thanks in Advance!!!

Best Regards,
Karthik0008

After struggling with the Calendar 12.0 utility in Excel 2007, I realized that what I really needed was a non-ActiveX date picker. I have a spreadsheet that requires a pop-up calendar for user-entered dates in a number of columns. Browsing through this forum, I found the following thread http://www.ozgrid.com/forum/showthre...picker+control which is great! It does exactly what I need to do, and works fine when I download it into a spreadsheet. My problems is that I can't use the export file utility for the Forms, Modules, and Class Modules - because I can't pass executable code (specifically, the .frx code) through my company's firewall. I decided to manually recreate the forms by walking through each of the dropdown objects. I took the source code from each of the modules, class modules, forms, and the ThisWorkbook code. So - everything loads on my blank excel, but I don't get any of the dates filled in on the calendar. And, I'm getting strange actions on the blank worksheets (not being able to right click, for instance). I've done something wrong, but I don't quite know where to start to figure it out. Can I not do this manually? Is there some way that I can recreate the Calendar and DatePicker objects? Sorry for the reference to the other thread, but I think you'll need to look at that code to understand what my problem is (and I don't want to reattach code that's already in Ozgrid).

Dear experts,
I have a form in Excel that I use for data entry. The data then gets to the Excel spreadsheet.
I use the Date Picker to collect dates. For some reason when I add a new record to the database, the date becomes just 12:00:00 AM for any date entered. The column cells are formatted as date.


	VB:
	
c.Offset(0, 15).Value = .DTPicker1.Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have also a test to return either "" or a value from the Date Picker depending on the condition. Still I consistently get the date or 12:00:00 AM from the Date Picker.

	VB:
	
 
    c.Offset(0, 24).Value = "" 
Else 
    c.Offset(0, 24).Value = .DTPicker2.Value 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Why is it happening? Does it have to do with properties of Date Picker?
Thank you in advance!

I am trrying to insert a date picker in an excel file, similar that which is
used in Infopath. Is this a possibility of impossible?

I am looking for a date picker (popup calendar to fill in date fields)
for Excel 2004 for the Mac. Most of what I'm finding is based on
ActiveX, and as such won't work on the Mac. Any recommendations would
be appreciated.

I am trying to create a form in Excel and I was wondering if anyone knew if
there was a way to add a Date Picker Box to one of the fields?

Hi,

I am trying to set up an Excel form with Pop-up Date Picker to use within my company but not quite succeeded.

It's required to attach with the form for people that don't really know how to install date picker macro in personal.xls.

I hope to find a easier way to do this.

Thank you.

Does anybody know if it is possible to change the background colour of
the "date box" of a Microsoft Date and Time Picker 6.0 COLOUR - by
"date box" I mean the combobox type object that you see when you this
control does not have the focus

(I know how to change the background of the dropdown calender, it's
just the box that returns the date selected, that I'm having fun with)

Any help greatly appreciated.
Jason

Hi, I've been using the Samrad date-picker for ages and it has worked like a charm. Just upgraded to Excel 2010 x64 and it is no longer supported. His email doesn't seem to work anymore...

Does anyone know of an easy to use date-picker add-in for Excel 2010 x64 without me having to insert VBA code. Just an add-in to copy into the XLSTARTUP folder or something as easy as that. Preferably free - If I am not asking too much.

Thanks so much.

I am using a couple of Microsoft Date and Time Picker 6.0 (SP4) controls within my Excel workbook. I would like for these controls to automatically fetch the current date. How can I do this?

Sorry if this is within the wrong subforum.

Hi,

Can anyone tell me how to add date picker to additional controls. system configuration below.

OS - Win 7 Pro 64 Bit
Excel 2010 - 32 bit

Thanks
R

Excel stores its type Date (date/time) values as a serial number containing an integer part representing a date and a fraction part representing a time value. My question is, what are the units for an Excel date/time value?

I’m interested because I often use what’s called dimensional analysis to help me assemble a formula in Excel. Dimensional analysis requires you to substitute the units of each item in an equation or formula for the corresponding numeric value. This technique can help you decide whether to multiply or divide two items when it isn’t apparent which you must do. It also helps determine if you need to add a constant like 60 minutes/hour to make things work out.

For example, you know the distance (in miles) that your car traveled on a trip, and you know how long it took to complete the trip (in hours). To determine the average speed the car traveled, you must divide one of those numbers by the other … but which one goes on the top?

Well you know that the end result has the units of speed (miles per hour or miles/hour). That’s a strong clue that when you divide, the distance value should go on the top of the division and the time should go on the bottom. So the formula would be = miles / hour. It’s not always so obvious when you are trying to figure out a complex formula.

For this technique to work, you MUST know the units for ALL factors, In the problem above, the unit of distance was specified as miles and the unit of time traveled was specified as hours. Suppose that instead of recording time traveled as, say, 30 minutes, you typed into your Excel spreadsheet 0:30:00, which is the Excel date/time format for 30 minutes. In this case what would the units of that stored value be? No, it’s not minutes. Reread the first sentence above about how Excel stores a date time value.

I have just about concluded that the unit of an Excel date/time value is “day”, but I’m unsure about that. Does anyone know for sure?

Hi,

Just looking for some advice...

I'm setting up a checklist in Excel - basically list of tasks with an ActiveX checkbox form control in the worksheet for each one. Next to each checklist item, I'd like the date that the item has been checked off to be which I'd like the user to pick from a calendar.

I've made a simple userform with the Date/Time Picker and buttons to say 'OK' and 'Unknown' - the idea is that when someone checks the box, they are automatically prompted to enter the date which they completed the activity then clicking 'OK' puts this date in an adjacent cell while clicking 'unknown' would make it just "-" or something. My problem is that I'd like to get this date into the cell adjacent to where the checkbox is on the list. I can't think of a way to do this without making a seperate userform for each item on the checklist to call up and upon clicking the OK button, populate the specific cell seperately for each individual checkbox with it's own unique userform.

I can call up the same date picker form every time any checkbox is true which I can do from a macro for each checklist item from the 'checkboxX_click' macro then IF checkbox is true, SHOW my date userform. The problem is then how to get this date into the right cell for each item on the list. Can it be done using one standard form?

After much looking around on here, I can easily get the date picker form to show and go into active cells by double clicking on them or I could easily put a DTPicker control next to each checkbox on my worksheet but ideally I'd like the user checking the box to prompt for a date which then appears next to the box without them having to check the box then click on a cell to enter the date which they would likely not bother doing.

Can anyone help?

I would like to add a date picker to an excell sheet is there
an easy way?
thanks for your help
tab

Return Day Of Week Of Excel Date
Return Day Of Week Of DateReturn Day Of Week Of DateWhen finding the weekday associated with any date, most of us would rather see it returned as a name of the day, rather than as a number (the default).Here we show how to extract the weekday of any date by using the WEEKDAY function. By default, the day is given as a whole number, ranging from 1 (Sunday) to 7 (Saturday). However, this is often meaningless, and we usually would rather see the weekday returned as a name, such as Monday/Mon, Tuesday/Tue, and so on.Get the Weekday as a NumberBefore getting to the name of the day, we'll need to begin by extracting the number of the weekday. Here's the syntax for the WEEKDAY formula:WEEKDAY(serial_number,return_type)Lets say we want to return the weekday number of 31-Jul-2007 The serial_num is any valid date in this case 31-Jul-2007 and the return_type is a number that refers to the type of return value. Depending on what result you are looking for will determine the return_type that you will use:return_typeDay of Week
1 or omittedNumbers 1 (Sunday) through to 7 (Saturday)2Numbers 1 (Monday) through 7 (Sunday)3Numbers 0 (Monday) through 6 (Sunday)We will use the default function by omitting the return-type. Click in cell A1 and type in a valid Excel date, such as 31-Jul-2007 (which is a Tuesday). Then, click in cell B1 and enter the following formula:
=WEEKDAY(A1)This formula will return the number 3, which equates to Tuesday, which is the day of the week that 31 July 2007 is.An alternative would be to hardcode the date like this:
=WEEKDAY("31 Jul 2007”)Return the Weekday as Weekday NameRemember, the WEEKDAY formula shown in the previous section only returns the weekday as a number. There are at least two ways we can use formulas to force Excel to show the actual name of the weekday.
The first method is perhaps the simplest, and all you need to do is apply a custom number format of DDD or DDDD. Again using 31-Jul-2007 as an example, select the date cell, right-click, go to Format Cells, and then choose Number tab>Custom. Enter in the custom format DDD under Type and click OK. You will get Tue in your cell.Another, probably safer way is to reference the date cell (e.g., =A1) and format this cell with a custom number format of DDD or DDDD. The big advantage to this method is that it leaves our true underlying date as a valid Excel date.
 Return the Weekday as Weekday TextIf you won't be using the weekday that is returned in any further calculations, you can use either of the three formulas that follow (TEXT, WEEKDAY with CHOOSE) to return the weekday of a date as text.This formula assumes you have a valid date of 31-Jul-2007 n cell A1:
=TEXT(A1,"DDDD") will produce Tuesday, or you could hard code your formula like:
=TEXT("31 Jul 2007","DDDD")
you could use the slightly longer CHOOSE function to get the same result:
=CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")and finally, you can hard code the date in the CHOOSE and nest the WEEKDAY function within it like:=CHOOSE(WEEKDAY("31 July 2007"),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday") ")All of these formulas will return the same result - Tuesday, remember though the underlying value of your cell will still be 31-Jul-2007, even though your cell reads Tuesday.

Hi All,

I have cells that have the time in seconds - 25340. I need to convert this into the date format. 07:02:19 (0.293275463)

i.e 25340 / 60 (to convert to minutes) / 60 (convert into hours) = 7.03

Now the raw time of the 07:02:19 is 0.293275463 so how do I convert the 25340 to the excel date values.

Regards

Chezney Atkinson

I am trying to make some forms, and what seems like it should be the simplest thing (a date picker) seems to be beyond my powers :P. I basically want to spots in the first worksheet where the user picks the dates, and I want the dates he picks to be copied over to the second worksheet (another form which uses data from the first).
I don't know how to make the "Microsoft Date & Time Picker" Active X control print to a cell on the second sheet. But there is another problem: The font and size of the boxes seems to increase to monolith 2" high boxes for no apparent reason. This probably has to do with my overall ignorance of ActiveX programming and VB, but nothing I looked at seemed to indicate 'random resizing'.

If anyone could suggest solutions or a better way of getting a date picker on to these forms, I'd be grateful.

- Thanks,
RJ Moore II

I needed to find a way to have employees select a "Start Date" in the worksheet. I've inserted a "Date Picker" (listed as SEP Date Control in the More Controls area).

I am able to select the date when I use the form. Then I save it and close it. When I re-open the form, it doesn't save the date.

How can I allow people to choose the date, and have it populate and remain in the field?

Thanks,

Hi All,

Take a look at the file attached; I am trying to design a charting tool that can graph up to five sets of information using combo boxes and date pickers. I would like to display a multiple line chart in another userform and have the ability to update every time a new set of information is added or taken away via the combo boxes.

So if I select a date range between 21/11/2008 to 11/11/2009 and then selected the criteria’s from zone 1, zone 2 etc and then clicked the show graph button, the new userform would pop up and display this information in a multiple line chart.

I would also have the ability to update the chart live, so if I went back to userform1 and selected another zone then the chart would update automatically to reflect this change.

If it is too much to ask for this chart to be displayed in another userform, then a multiple line chart in excel, embedded on a worksheet would be fine.

Thanks for your help.

I'm trying to add the date picker to a range of cells per the instructions on a msdn developer web page and am getting a syntax error, can someone help

Private Sub ExcelRangeAddDateTimePicker()

Dim DateTimePicker1 As Microsoft.Office.Tools. _
Excel.Controls.DateTimePicker = Me.Controls. _
AddDateTimePicker(Me.Range("A1", "C1"), _
"DateTimePicker1")
DateTimePicker1.CalendarMonthBackground = _
Color.LightBlue

End Sub

Here's the link

http://msdn2.microsoft.com/en-us/lib...1h(VS.80).aspx

Is it possible to get a DATE PICKER?

In Visual Basics, we get a Date Picker...Is it possible to get it in an Excel File?

There are so many instances when we have to enter the Date...this can be avoided by providing A Date Picker...

What Im looking at is to have something appear in the cell once you have selected the cell like the way we use a Dropdown List..

I think this can be acheived with the help of Visual basics...which I dont know...

Thanks in anticipation..

all4excel

I have 2 date/time pickers that are on the 3 page of a multipage on a userform.

For some reason, when you switch between pages, the 1st picker moves from
it's locations (left 198, top 66) to the far upper left corner of the form.
I have tried adding code to move it back whenever the multipage changes,
however, it doesn't work (despite the fact that the event triggers other
aspects of the code). Any ideas as to why its moving on me?

Private Sub MultiPage1_Change()

' Page 3: Set the date picker default value to the first of the current year

If MultiPage1.Value = 2 Then
With UserForm1
.DTPicker1.Value = DateSerial(Year(Date), 1, 1)
.DTPicker1.Left = 198
.DTPicker1.Top = 66
.DTPicker2.Value = DateSerial(Year(Date), Month(Date), Day(Date))
MsgBox .DTPicker1.Left
End With
End If

End Sub


No luck finding an answer? You could always try Google.