Date Entry Without Typing Dashes?
Feb 28, 2006I want to type "121505" in a cell and it display 12-15-05. What formatting do I use?
View 4 Replies
ADVERTISEMENT
How To Keep Date Entry TODAY Frozen On That Specific Date
Mar 6, 2014I am working on a spreadsheet to create a school 'tracking' system based on excel. What I need is that as soon as a box in column B is ticked, a date (TODAY() is entered in column A. That is not so difficult, and I have used the formula in cells in column A:
=IF(ISBLANK(Bx);"";TODAY()), where x is the specific row.
So, keep cell A clear untill something is written in cell B. This works fine.
But, for the purpose I need to keep the TODAY() date fixed after entry. And it need to be erased again if the tickbox in cell B is emptied again. It need then to re-enter a new date after cell B is re-used.
Restrict Date Entry To Be With Date Range/Scope
Jun 19, 2008I'm having a problem with data validation. I set an entire column so that it could only be a date between the first date in the list and the current date. I tested a few cells to make sure that it was working the way I wanted and I noticed that it allows a random number like 3 or 5 to be input after row 50. This would create a date in the 1900s. Why is this happening and how can I stop it.
Remove All The Dashes
Jun 14, 2006Have a column containing data which may have 1-3 dashes locates within
each cell value.
Such as:
125-457A
158-857-1116
124184A-1
125-8745-22D9
458-2145-002-7
Would like to remove all the dashes "unless" the last dash to the right
is followed by a just a single digit. The results would be as follows:
125457A
1588571116
124184A-1
125874522D9
4582145002-7
On the example data, it kept 2 of the dashes in the cell values,
because the were only one digit away from the extreme right, but
removed all others.
Want To Add Dashes To Cell Text...
May 11, 2009I have a column of city names, some are one word and others are two or more. I am trying to take the city name and add it to another word, add dashes in between them and make everything lower case. For example:
Column 1, Cell 1 is: San Diego
I'd like it to look like: books-san-diego (books being the keyword I'd like to insert before the city.)
Return Cells That Have Exactly 3 Dashes
May 27, 2009I have a column of data (column A) with cells that have IDs (alpha-numeric) that contain anywhere between 0 to 5 dashes. Since this column will change weekly, I want to dynamically pull only those cells that contain 3 dashes.
Example:
10648145-3026718-t526363536m
09wcg7-s1s89-wtpxm33-uu6jj0-9y8mv-v1
10658575-2349769-
I would only want to have returned 10648145-3026718-t526363536m since it contains 3 dashes. Do you know of a nifty formula for doing this? I think an array would get me the dynamic piece (so they are not spaced out).
Look Up On Cell But Based On Value Between Dashes
Jul 6, 2007Here is the examples of values i have
TCH1 - John Smith - I - REG - Total
AV - John Doe - D - REG - Total
what i want to do is lookup on the value after the first dash and before the second dash to get the names only ; John smith, john doe
Remove Dashes In Number Automatically
Jan 13, 2008I have a column of 1000 folio numbers and need a formula that will remove the dashes like so,
35-30-19-041-0180 into
3530190410180
Removing Dashes Deletes Everything After First Dash
Mar 25, 2013I have entries that look like this - 300-7188258-1
300-8983980-5
I need to remove the dashes. I find and replace all - with nothing. that has worked for YEARS. Seems simple to me.
Today, when I do it, Excel leaves the "300" and removes everything else. I even did a CLEAN function and it still does it.
Format Cells With Dashes Between Alphanumerics
Nov 15, 2006I'm trying to find a way to have excel 2003 format the data I put in a cell to auto insert dashes every five characters. I'm entering product keys for the software we have on hand, and it would be a little quicker if I could simply have excel auto format the data entered in to include dashes every five characters.
Example:
- I would enter: D5ATT3D28F6F44536489413E2 (This is a fake, non-working product key, I just typed in random numbers a letters.)
-Excel would format it like: D5ATT-3D28F-6F445-36489-413E2
How Do I Get The Dashes To Not Count As A Digit/number?
Sep 12, 2007I have this script that does what i need it to do but one thing, I need the script to count the numbers as digits only and not the dashes in the format.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Intersect(Target, Range("I21:I24")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If (Len(Target) > 11) Or (Len(Target) < 10) Then
MsgBox "Not a valid number of characters, please try again"
Target.Value = vbNullString...................
How To Remove Specific Dashes Within Cell Leaving Others
Jul 28, 2014I have a series of columns that have dashes in them and want to remove just specific dashes from the cell
Example of original 70489-344-15-001
This is what I want --> 70489-34415001
Got about 1000 of them ...
Date/Time Formula: Pick Up A Date With Time Entry On A Worksheet And Place It Into A TextBox On A UserForm
Jun 17, 2006I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the
Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
Else
TextBox2.Value = ""
TextBox2.SetFocus
End If
End Sub
"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?
Hyperlink Code For Sheet Names With Spaces Or Dashes
Sep 7, 2007I believe I have 99% of what I need. The code below has one bug. (See just the underlined text for a very quick overview. The rest is detail.)
My goal is to generate a TOC that:
1) has the VBA run from my Personal Workbook.
2) can be run in any worksheet of any open workbook.
3) can be run in a worksheet of any name.
4) will insert itself at the currently active cell.
5) has entries hyperlinking to the referenced worksheets - of any name.
Right now, I'm stuck with the last point. The macro below will generate the TOC at the currently active cell. The TOC will be hyperlinks to the referenced worksheets.
However, if the referenced worksheet contains a space, dash, or other special character then the generated hyperlink is broken. For example, if the referenced worksheet is "sheet1" then the hyperlink generated works just fine. However, if the referenced worksheet is "sheet-1" then the generated hyperlink doesn't work.
Sub IndexList()
Dim objSheet As Object
Dim intRow As Integer
Dim strCol As Integer
Set objSheet = Excel.Sheets
intRow = ActiveCell.Row 'Start writing in active row
strCol = ActiveCell.Column 'Start writing in active column
For Each objSheet In ActiveWorkbook.Sheets
Cells(intRow, strCol).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
objSheet.Name & "!A1", TextToDisplay:=objSheet.Name
intRow = intRow + 1
Next
End Sub
Custom Number Format - Automatically Enter Dashes At Appropriate Spot
May 7, 2014I am needing a custom format for the following. I need a cell entry to be 1 letter, three numbers, a dash, 5 numbers, a dash, two numbers, a dash, and then two number. Example: A109-54785-13-00. The first letter will almost always be an "A."
To further complicate the matter, the entry is copied from an email and pasted without the dashes. In the above example, the number in the email would read, "A109547851300." I copy it from the email and paste it in the proper cell in the worksheet. I would like the custom format to automatically enter the dashes at the appropriate spot.
If it was all numbers, this would be easy. I created a custom format for the numbers, but when the A is included, Excel no longer treats it as a number and the custom format did not work.
Is this possible in Excel (without VBA).
I could use the custom number format I created and then later go back and add the "A" at the beginning, but that is as much work as manually adding the dashes.
Validate Date Entry
Mar 27, 2014I have a ComboBox on a UserForm. One of the fields that I fill in is a date.
After the date is entered I want to check if it's a valid date.
VB:
Private Sub cboEnterDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' Make sure a valid date was entered, it must be in the format 2013/10/21.
On Error Goto ERR
'If Not IsDate(cboEnterDate.Value) Then
[Code] .....
ERR:
MsgBox "The date entered is not a valid date", vbInformation
Cancel = True
cboEnterDate_Enter
End Sub
The code:
VB:
'If Not IsDate(cboEnterDate.Value) Then
execute the If routine if I enter the date 2014/03/33
When I enter the date 2014/03/0001 it do not see it as an invalid date and exit the routine.
The code:
VB:
If Not IsDate(FormatDateTime(cboEnterDate.Value, vbShortDate)) Then
send the execution to the ERR routine if I enter 2014/03/33
When I enter the date 2014/03/0001 it do not see it as an invalid date and exit the routine.
How can I get the validation to catch the 2014/03/0001 as an invalid date as well.
Date Entry Using VBA Code
Dec 31, 2008I am not sure how many questions I can ask in one day, but I have one more. I am using VBA code for easy date and time entry on a spreadsheet...I am a complete novice at this.
I need to add additional columns to the range for dates and times.
also, is it OK to use the code for date and time on the same worksheet?
Code for Date:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim DateStr As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("A2:A100")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Date Entry Renders
Jan 14, 2009Is there a way that I can set up two cells so that when a date is entered into one, the other renders something like "Reporting from XXXXX to YYYYY" where XXXXX is the Monday of that week and YYYYY is the Friday of that week?
So, for instance, if the user entered 1/14/09 into cell A1, then cell B1 would show "Reporting from 1/12/09 to 1/16/09"...
Remove Duplicate Entry Of Date
Jan 22, 2014I am trying to remove the duplicate entry of the date the list goes like this
01/22/2014 6:10
01/22/2014 6:12
01/22/2014 6:13
01/22/2014 6:15
01/22/2014 6:17
01/23/2014 7:00
01/23/2014 7:01
01/23/2014 7:02
01/23/2014 7:03
But I want
01/22/2014 6:10
01/23/2014 7:00
I have attached a file where I need to clean up column A but still maintain the rows sheet one is what I have sheet 2 is what I am hoping to achieve.
Automatic Date Entry In Cell
Jul 16, 2008I have a workbook with several sheets, each of which represents a client. At the bottom of this sheet, I have a call log to record the times that we attempted communication, left a message, etc. Since it's tedious enough as it is, I'd like to save my team 2 seconds and a slight bit of that tedium by having the date an attempt was made automatically recorded in the row where the attempt is entered.
Let's say this all takes place in A47:D47. For example, if I called the client on 7/15/08 and left a message, I would record who was called in B47, the name of the caller (me) in C47, and the result in D47. If possible, I would like to have the date of the attempt (today's date on the given day) entered in A47 upon the entry of information in B47:D47.
Static Date - Automatic Entry
Dec 12, 2011I am attempting to create a formula which will detect of a cell is empty, and if it is empty enter the date by using TODAY(). I also dont want this date to change when opened after it has been entered. This is what I have been working with so far.
Code:
=IF(A1="",TODAY(),"")
Code:
=IF(G9>=TODAY(),A1=1,"")
I am trying to use the second formula to change the value in the cell "A1", but instead it just returns a true/false statement in the cell with this formula.
How To Get Automatic Time And Date Entry
Jan 12, 2012How can I get an automatic time & date entry into cell B1 based on a alphanumeric entry in cell A1. The time and date must not re-calculate every time the workbook is opened.
View 4 Replies View RelatedCode To Format Date Entry
Jun 9, 2006I have a macro which is designed to collect information and then assign each piece of information to a defined cell. I have Dim statements for each piece of information i.e.
Dim Info1 = Surname
Dim Info2 = Initials And so on.
Dim Info11 = Date of entry
This is working perfectly but the date when entered is reversing to american format. 08/05/2006 becomes 05/08/2006. Type into the cell manually and it works perfectly let the macro put the date in and it changes format. Can anyone supply a line of code that would format the date to dd/mm/yyyy? Then I could insert it after the part of my code which basically says go to this cell and put in the date.
Warning Message Box For Date Entry
Aug 15, 2006I am writing a roster spreadsheet and it has cells with drop down lists using validation lists to fill in the start and finish times, as we run a nearly 24 hour business it is possible to accidently start someone at 2.00am and finish them at 10.00pm. In the cell which calculates the time rostered I want it to display a messagebox if the hours exceed 10 hours or go into negative.
I need to "wife proof" this sheet as this stuffs up the total calculated hours and total costs.
Would I do this through validation on the cell with the formula calculating the times orr though a macro on the sheet module?
Ensure Valid Date Entry
Jan 9, 2008When a user inputs a month and then a day, I want to be able to check to make sure that the day entered is possible in that given month. Is there a way to do that which is not too complicated?
View 9 Replies View RelatedDate Converting To 1/0/1900 On Entry
Jan 30, 2008I have a old spreadsheet file which I opened up in Excel 2003. It has a few worksheet tabs. 2 worksheet tabs seem identical. I will reference it as Worksheet1 and Worksheet2. In Worksheet1, I enter a date i.e. 1/25/2008 in any cell. It enters as inputted. But in Worksheet2, I enter the same date i.e. 1/25/2008, it appears as 1/0/1900. No matter what date I enter in Worksheet2 in any cell, it always appears as 1/0/1900. Now when I look at cell in the formula toolbar, it sees as a Math Formula meaning, it looks like this: =1/25/2008. How do I correct this Worksheet to recognize any date inputted, not to see it as a Math Formula?
View 4 Replies View RelatedPick Pre-determined Date Entry In Userform?
Aug 11, 2014I am trying to get my User Form to work whereby I choose a pre-determined date entry. I have tried some coding but cannot get it to work. What code I need to add in VBA for this to work? I have tried to alter the time entry from my previous post that was solved but are doing something wrong.
View 11 Replies View RelatedDisplaying Date And Time At The Moment Of Entry
Dec 12, 2011I have cells on my spreadsheet which contain: =now() and they work as they should - displaying the date and time at the moment of entry. Unfortunately the cells update when other info is added at other locations on the sheet - is there a way to ensure they enter the current 'now' only at the point of initial entry - such that the data doesn't then alter?
View 3 Replies View RelatedVBA To Change Cell Entry Into Time And Date
Feb 17, 2012I am looking for a formula or VBA that will be in each in cells D5:D300 and will look at the value entered into the cell in column C and change the 4 digits into the date and time in the same format as NOW() returns.
Example
2045 becomes 17/02/2012 20:45
But I would also want it to see if the 4 digits that represent the time are after 14:00 and before 00:00 then the date would be the date in cell A1-1
example
Cell A1 18/02/12
2045 becomes 17/02/2012 20:45
Auto Entry Date When Enter Data?
May 28, 2014What I want to do is if I add data into B7 or F7 then the current date will appear in J7. I also don't want the date to change every time the project is opened.
Thus,
If I enter "withdraw or any word" into B7 or F7 - The current date (4-Dec) would appear in J7.
then if I close excel and I open it tomorrow, J7 would still read as 4-Dec, while if I enter "deposit or any word" into B8 or F8, the current date (5-Dec) would appear in J8.
- would need to be able to enter multiple entries on one day as well.
- also i am not familiar regarding macros.
