Solved

Input of date and time into Excel

Posted on 2011-03-09
17
393 Views
Last Modified: 2012-05-11
Hello:

We have an excel spreadsheet with a few fields that require the user to enter a date and time.  Example:  3/9/2011 1:14 PM.  We have the cells formatted as date and time but I was wondering if there is an easy way to help with the entry.  Like and input Mask in Access.

Example:  User enters 3092011 114pm and the cell shows 3/9/2011 1:14 PM

I just want to save them some key strokes.

Thank you,


0
Comment
Question by:MeowserM
  • 7
  • 6
  • 4
17 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 35085977
It would be possible to create a macro to do that behind the scenes, but it can get tricky.  
A better way could be to pop up a form that asks them to click on a calendar and clock for the day and time, but I haven't seen a clock control so it would have to be written.

Otherwise an input box could be placed on top of the cell to minimize the macro coding, then push the value into the cell.
0
 

Author Comment

by:MeowserM
ID: 35086074
Thank you for the response

I haven't seen an input box before...how do you set one up?

0
 
LVL 5

Expert Comment

by:wellous
ID: 35086081
Hey MeowserM,

Would like something like that calender ?
Pls click on cell B2 and enter the date/time you want.
Hope that helps,
Brgds ,
Wellous
date-timepickerver1-1-.xls
0
 

Author Comment

by:MeowserM
ID: 35086135
Thank you wellous -

But you still need to enter the / and : for the time.  I was hoping to avoid that.

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35086178
Meow, to get the input box, you will need to enable the develop tab.  It's different for different versions.

Excel 2003 and before, right-click on the menu and select Control Toolbox (or something like that)
Excel 2007 go to Office button (top left corner circle) and select Excel Options (bottom right corner) then one of the checkboxes should be Enable Developer tab
Excel 2010, File tab, Excel options, Customize Ribbon, and check-select the Developer option in the right-side list.

After that, for 2003, you'll have a toolbar which should contain an "ab" inside a box.  For 2007/2010, in the new Developer tab, pick Controls Insert and look for the same "ab" (the lower one, inside a box)

After clicking on it, you should be able to click on the spreadsheet or drag a rectangle with the mouse to place it.  Then you can move it as needed or resize it.  To active it, look for the little triangle with a pencil in the toolbar (2003) or control box (2007/2010) and click on it.  you now have a working text box.

the next step would be to program it to do something useful.

Meanwhile, see if wellous' sheet works for you.  it may require enabling the calendar control or otherwise importing it; I avoided that because of portability issues, but if it works for you, it's a much better solution.
0
 
LVL 5

Expert Comment

by:wellous
ID: 35086436
Thank you rspahitz ,

Hi MeowserM,
You don't need anything .. you will select the date and select the time in the calender box!
Brgds,
Wellous
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35086446
Here's some code to manage the text box formatting (assuming data entry similar to how you specified and assuming the result should be placed into cell I1:

Private Sub TextBox1_LostFocus()
    Dim strDate() As String
   
    '03092011 1314'
    strDate = Split(TextBox1.Text, " ")
        Range("I1").Value = CDate(Format(strDate(0), "##/##/####") & " " & Format(strDate(1), "##:##:##"))
End Sub

however, as indicated, this will take some work to build enough logic to handle all problem cases (like missing leading zeroes for the month, omitting the seconds, putting a 2-digit year, putting am/pm rather than military time, etc.  But that can all be done in the above "sub"routine.
0
 

Author Comment

by:MeowserM
ID: 35086816
Thank you both.

Wellous - I didn't get a calendar picker. I got an error when I opened the form that says "Could not load an object because it is not available on this machine"  That must be the calendar control.

How do I get that loaded?

Sorry I didn't catch it the first time....
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 22

Expert Comment

by:rspahitz
ID: 35088474
So there are several solutions.  I guess we need to find the one that you think is easiest for your users. At this point, Excel doesn't really directly offer the same Input Masking that Access offers.  It can be emulated in various ways.

Do you want it to check:
* in the cell directly
* through an overlayed textbox
* through a pop-up window that lets you select the date
* through a separate controlling window?

Each has its challenges and issues.
0
 

Author Comment

by:MeowserM
ID: 35094313
Thank you rspahitz.

My first choice would be in the cell directly.  

0
 
LVL 5

Expert Comment

by:wellous
ID: 35094598
To enable calendar control
Open Windows System Resource Manager. To open Windows System Resource Manager, click Start, point to Administrative Tools, and then click Windows System Resource Manager.

In the Connect to Computer dialog box, select This computer, and then click Connect.

In the console tree, right-click Calendar, and then click Enable.

Hope it helps

0
 

Author Comment

by:MeowserM
ID: 35095714
Wellous -

Thank you but I don't have permissions to enable that.  

Thank you for the information.  I'll have to keep looking.

0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 35096408
OK, so let's make this happen, and assume that the entry is in the format you specified (although I allowed variations), and assuming that entry is in cell C1 (row 1, column 3)

 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strStartingValue As String
    Dim strValuePieces() As String
    Dim strDatePiece As String
    Dim strTimePiece As String
    Dim strNewTimePiece As String
    Dim strNewValue As String
    
    If Target.Row = 1 And Target.Column = 3 Then
        If TypeName(Target.Value) = "String" Then
            ' only try to convert this if it's not already a date
            strStartingValue = Target.Value
            strValuePieces = Split(strStartingValue, " ")
            
            ' first determine the date piece
            strDatePiece = strValuePieces(0)
            Select Case Len(strDatePiece)
                Case 5 ' format mddyy
                    strNewValue = Left(strDatePiece, 1) & "/" & Mid(strDatePiece, 2, 2) & "/" & Right(strDatePiece, 2)
                Case 6 ' format mmddyy
                    strNewValue = Left(strDatePiece, 2) & "/" & Mid(strDatePiece, 3, 2) & "/" & Right(strDatePiece, 2)
                Case 7 ' format mddyyyy
                    strNewValue = Left(strDatePiece, 1) & "/" & Mid(strDatePiece, 2, 2) & "/" & Right(strDatePiece, 4)
                Case 8 ' format mmddyyy
                    strNewValue = Left(strDatePiece, 2) & "/" & Mid(strDatePiece, 3, 2) & "/" & Right(strDatePiece, 4)
                Case Else ' unknown format
                    strNewValue = ""
            End Select
                
            If UBound(strValuePieces) > 0 Then
                ' seems that the date and time are supplied; ignore any piece after the second
                ' first check AM or PM
                strTimePiece = strValuePieces(1)
                If UCase(Right(strTimePiece, 1)) = "P" Then
                    strNewTimePiece = " PM"
                    strTimePiece = Left(strTimePiece, Len(strTimePiece) - 1)
                ElseIf UCase(Right(strTimePiece, 2)) = "PM" Then
                    strNewTimePiece = " PM"
                    strTimePiece = Left(strTimePiece, Len(strTimePiece) - 2)
                ElseIf UCase(Right(strTimePiece, 1)) = "A" Then
                    strNewTimePiece = " AM"
                    strTimePiece = Left(strTimePiece, Len(strTimePiece) - 1)
                ElseIf UCase(Right(strTimePiece, 2)) = "AM" Then
                    strNewTimePiece = " AM"
                    strTimePiece = Left(strTimePiece, Len(strTimePiece) - 2)
                Else
                    strNewTimePiece = " AM"
                End If
                
                Select Case Len(strTimePiece)
                    Case 3 ' hmm
                        strNewTimePiece = Left(strTimePiece, 1) & ":" & Right(strTimePiece, 2) & strNewTimePiece
                    Case 4 ' hhmm
                        If Val(Left(strTimePiece, 2)) > 12 Then
                            strNewTimePiece = Left(strTimePiece, 2) & ":" & Right(strTimePiece, 2)
                        Else
                            strNewTimePiece = Left(strTimePiece, 2) & ":" & Right(strTimePiece, 2) & strNewTimePiece
                        End If
                    Case Else
                        strNewTimePiece = ""
                End Select
                
                If strNewTimePiece <> "" Then
                    strNewValue = Trim(strNewValue & " " & strNewTimePiece)
                End If
            End If
            
            If strNewValue <> "" Then
                On Error Resume Next
                Target.Value = CDate(strNewValue)
                On Error GoTo 0
            End If
        End If
    End If
End Sub

Open in new window

0
 
LVL 5

Expert Comment

by:wellous
ID: 35096607
Thank you rspahitz, and Meow, for learning me something new :)
To make it working in the all sheet , just remove line 9 and line 74 ..
rspahitz , Good work :)
Brgds,
Wellous
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35096688
This was a fun one, but I always hate changing the original data and pushing it back onto itself, just in case something goes wrong with the conversion...I usually like to put it into a holding spot (like a separate cell or textbox) so you can go back and modify the original if needed without having to re-enter the whole thing.

And as wellous said, you can have it apply to the whole sheet, but if you type something that looks like it might be a date, it could intercept it and change it without you realizing so that's why I limited it to a single cell.  You could also have it apply to a range of cells (like maybe everything in column 3, or only row 7 or just cells C1, C1, D5 and D6 by changing the first "If" statement (line 9)
0
 

Author Closing Comment

by:MeowserM
ID: 35097423
Wow.  This solution was much better than I had even hoped for.

Great Job - I wish I could give you more than 500 points

Thank you!!
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35097677
Feel free to contact me through my profile and send me gift checks :)
Glad you found the solution useful.  Like I said, it was a fun task.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now