I'd like to enter dates without having to use the forward slash symbol between the day, month and year values. I've read a post by Dave Hawley explaing how to do it, but I can't get it to work for me. There must be some step that I am not aware of.
View 9 RepliesTo explain I have two sets of merged data. On one fixed column I have two different sets of peoples age
1 - by age grouip eg 25 - 34
2 - By birth year so 01/02/1983
I know that person who is born between 1975 and 1984 would fall into the age group 25 - 34
I want to convert all the birth years into age groups. Its a lot of data (30,000 rows +) in random order
Does anyone have any idea how I would set this up, as I really want to add "age group" to a pivot table I have.
i built a form and in the date section i have a date picker and once i fill the form out i have a button that saves all the data on another sheet like a database, what do i need to do to make it were it adds the date to the database itll add all the other data but cant seem to figure out how to make the date display
View 3 Replies View Relatedall i need to write is a code which syncronizes with my update button, which i don't know how to write....whenever i click the update button, all my files will be updates, and at the top elft of the screen would show the time and date of my last update.
View 6 Replies View RelatedI am trying to insert a formula that counts the number of dates within a certain date range. why this sample file does not work, the answer I am looking for in cell C1 is 3.
View 2 Replies View RelatedI have a user form with 2 textboxes (used as parameters for an ODBC query). The entry should be a period, as yyyymm. How can I check, for instance when clicking the Ok button after entry, that both textboxes are of the "000000" format and the last 2 numbers are between 01 and 12?
View 9 Replies View RelatedI would like to have some kind of input mask on my input boxes, the input box is for currency IN GBP (£)
The program itself obviously has no use for the £ sign, but I would like it to display in the input box as a mask.
Is this possible?
I should mention I am using:
Application.InputBox
I need the users to be able to enter the time using either a . or a colon :,
So: 13:30 would give 13:30 and 13.30 would give 13:30. They have end up in time format too since I will be performing calculations on them. When that decimal place is entered I would like it to be turned into a dot
Basically I want to be able to type a number and have a colon inserted two spaces to the left. So 123 would become 1:23, 1234 would be 12:34 and 12345 would be 123:45.
I'm working with anywhere from one to several thousand hours in a spreadsheet. I'd like to be able to use a time mask like this:
how can i show asterixes instead of the input data as it is being typed in a cell? somewhat like a password box...
View 6 Replies View RelatedWant to enter 6 digits for each cell in a range of cells with VBA mask change to time format hh:mm:ss for each cell when entered. Code below does this for hours and minutes but I haven't been successful in modifying to work including seconds.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
With Target
vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
Application.EnableEvents = False
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End If
End With
Application.EnableEvents = True
End Sub
I have entered VBA code and tested it with success. If I then save & close the excel file and then re-open it, the mask time entry is not saved (time does not appear correctly after input) and per instructions I have designated (and am using) the cell input range.
View 9 Replies View RelatedI’m trying to write a VBA script to create disguised prices in an empty column “A” by extracting, and adding additional random generated characters (alpha, numeric, extended character set) from adjacent column “B” to create a scrambled price sheet.
Example Data: (2 columns and 5 rows)
Column A = newly scrambled prices. Column B = product prices.
Input: Column B = $13.95, $5.99, $4.00, $12.50, $10.10
Output: Column A = XCP1D3-VG9S5T.1L1, HUI0F5-SJ9W9X.9A6, RTJ0K4-JM0D0Z.4B4, KET1S2-AK5G0B.3M2, DLE1S0-QK1G0M.0C0...................
I have a long list of data (around 10,000 rows) with one column Client Sensitive (Column A see below) that needs to be changed before using in a pivot so the client won't see this data.
At the moment I'm using a formula but happy to use VBA for this as the rest of the report updates with VBA.
The formula that works but takes far too long is
=IF(COUNTIF($A$1:$A2,$A2)>1,VLOOKUP($A2,$A:$R,18,FALSE),MAX($R$1:$R1)+1)
Where column A is the sensitve data (and the unique identifier) and column R is the column the formula is entered.
The below is what I want, the column R I now use in a pivot to get the results I want then I can hide that column and delete Column A so no Client Sensitve data is with the spreadsheet.
However as I've said it takes far too long to calculate down 10,000 rows..
Column A
Column R
A12345
1
[Code] .....
how I can create a custom mask that will always put a / at the start of each cell in column C. So basically when I enter a number in, say 350, the mask will automatically put a / in front of it - /350 or even better /space350. The only issue is that the number will change in length a little, so not sure if this is a problem or not.
View 4 Replies View RelatedIs there a formula that I can put in AI that will mask the text character from text position 3 through the 2nd to last text position of each business name in AH? Where in the heck do you find XLGenie in the Excel 2007 ribbon? I have it installed and its in my addins list, yet nowhere to be found in the ribbon!
AH
13
ABC Rental
14
Professional Tool Service
15
Wonder Plumbing
16
Jim's Bar & Grill
17
Bobs Hair Salon
I have a simple table with these column: Name, Surname, Address, Date of Birth, etc.
I would like to create a button which open a mask to insert the above data "automatically" in the table/columns.
I tried playing, being able to create the mask UI, but it doesn't work: the command "OK" doesn't insert the data. I am sure it is a stupid thing, but I can't sort it out.
I've got a list of 300 IP addresses in Column A.
I want a formula which will return the router, mask and broadcast IP
Eg. If i have 192.168.1.129, the result are:
router = 192.168.1.1
mask = 255.255.255.0
broadcast = 192.168.1.255
How I can go about finding missing entries. I have an excel sheet that has column A as employee name and column E with a date. There are 10 employees so there should be 10 entries for each date. I need to be able to determine which employee hasnt entered information for a given date.
View 1 Replies View Relatedhighlighting the duplicate entries in the same date comparing C:C data.
View 3 Replies View RelatedI'm looking for a way to count entries of numbers per day of the week within a date range. Example
Start End Mo Tu We Th Fr Sa Su TOTAL
2/10 2/23 1 1 1 1 0 1 1 12
3/1 3/31 1 2 1 1 1 1 2 40
I don't know if that possible in excel or its to much
Basically each line signifies a date & time that a deposit was placed and a date & time that a withdrawal was actioned. My goal is to be able to determine what kind of average response time we have between the time a withdrawal was requested and the time a withdrawal was actioned.
View 2 Replies View RelatedI have this data set which has customers D.O.B's. This a test data set for the MGM Grand Casino and some customers are under aged below 21 or not even born yet (basically wrong inputs). So ultimately I want to retain the row entries of the customers who were born between (1930 - 1992).
mgm_cleaned TEST Â ABCDEFGHIJKLM43928-Sep-20048-Sep-200405.4722000004-Oct-194944969-Sep-20049-Sep-200408.5720.25000004-Oct-1949459320-Apr-200423-Apr-2004010.9255000004-Oct-1949469420-Apr-200420-Apr-200409.2941000004-Oct-1949479121-Apr-200421-Apr-2004019.637.25000004-Oct-1949489221-Apr-200421-Apr-2004016.2941000004-Oct-1949499323-Apr-200323-Apr-2003010.96-10.25000004-Oct-1949509623
[Code] .........
The cell content is to be 0-7 characters in length.
Alpha characters only, i.e., no numeric.
First character must be uppercase.
Remaining characters may be lowercase or blank.
Is that possible to do in MS Excel 2007?
I have a column formatted as a date. I would like to filter out all rows which have a blank date value. I don't see any blank value to select when I try to select auto-filter for that row. Any suggestions?
I would like to filter out the blank values because I found that if I pivot on the data and there are blank values in the field, I can not use the group option for that field.
I have a column (D) where I put percentages complete of a task. When that reaches 100% I want to put the date in column (C).
I found many samples online but nothing I can modify to do this.. This one is pretty straight forward but I'm not sure how to have it look for the value and not just empty or not. I would be greatful for any help you can offer.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target(1)) Then Exit Sub
If IsEmpty(Target.Offset(0, -1)) Then
Target.Offset(0, -1) = Date
Target.offset(0, -1).numberformat = "yyyy-mm-dd"
End If
End Sub
How do I stop a pivot table adding data together?
e.g.
1st Nov -100
1st Nov - 100
2nd Nov - 200
2nd Nov -200
I want this but instead get this:
1st Nov - 200
2nd Nov - 400
I need the pivot to split it out,
How to perform this function on Google Spreadsheets (which appears to be slightly different to Excel);
Calculate the sum of the numbers in column D that occur on the 10th January 2014?
And also, Calculate the sum of the numbers in column D that occur between 9am and 10am on the 10th January 2014.
I've been struggling for hours on what should be a simple formula. I have 6 columns containing various dates. On each row I want to count of the 6 columns how many dates were unique and after 3/15/09. I've been using the following formula however it still counts a cell even if it's prior to 3/15/09. =SUM(IF(FREQUENCY(A1:F1,A1:F1)>3/15/2009,1,0)). I've attached a sample file for reference.
View 2 Replies View RelatedPlease refer to attached spreadsheet
I receive data from an external source and it displays what appears to be dates in column B.
In column G I apply a formula to display month/year info.
It turns out that some of the data in column B is valid date data, but other data imports as plain text and therefore I can't get the month/year info that I require.
I have attached just a small example.
I get this data monthly and it usually covers thousands of rows and therefore impractical to change manually.