I have a mapping program that uploads data with the date and time but when it exports it the time is converted to just a number string, e.g. 15:12:37 becomes 151237000. Is there way to eliminate the last three zeros and insert a ":" between the second and third, and fourth and fifth characters?
View 8 RepliesI have a very large spreadsheet and want to count the number of times a particular text string shows up in a column. I can't use autofilter due to the 1000 limit.
Here's an example, Column C contains:
Dan Parker
John Doe
Dan Smith
Jill Smith
So if I search on *Dan*, the function should return a count of 2.
I've used COUNTIF before to return values when the whole cell = a certain value but in my case the cell may have 200 characters and I want to count based on a fuzzy search. I would like to do this in a function and not a macro.
I need a code that search through a column, counts the numer of times a string occurs and creates on another sheet the unique string and the number of times it occured.
e.g
I have this columns in a worksheet
s/n name company trainer
1 ball Dell Mk
2 doll Msed Kl
3 kol Dlink Mk
4 ball Msed Cl
5 Koll Dell Gl
I need the result to appear on another worksheet like this
Name NumberofTraining
Ball 2
doll 1
Koll 2
Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?
Example;
Cell A1 has 123XVF1234
Cell A2 has 321AFW4321
In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value
Example;
If B1 returns 643562 it would need to be able to be converted back to 123XVF1234
In sheet1 I have a simple database consisting of 5 columns of data
Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500
what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.
I need to create a string of all the Acc Number but I dun not wan any repeat of the same Acc number in the String . The number of Acc number to be put in the string may varies sometimes. It may varies from 3 to 1000 or more . Is there a way to write a general formula to create such string?
View 4 Replies View RelatedFor this example, A1 is the given at 19.875 I am trying to get A2 to net a result of 19.5. A2 is to net that result by searching through cells B1:B14 and finding the nearest equal or lower number. If A1 is less than 13.5 a result of "error" should be generated in A2.
B1=13.5
B2=15.5
B3=17.5
B4=19.5
B6=21.5
[Code]...
I am trying to provide a tool for department leaders to monitor productivity for order processing in their departments. The variables I have are: Number of orders(variable), number of pickers (variable), start time(variable). Then, I know each order takes 1 picker 4 minutes to pick on average, and there are 45 minutes worth of breaks during the picking process. So after entering the variables I used =(((C3*4)/60)/D3) to come up with the time needed to process the orders. What I can't get to is how to add this number to the start time, factor in break minutes and get to the projected completion time. I have Excel 2003 at work. Clearly I need to take a class!
View 4 Replies View RelatedI have a user form with textBox1 = start time (entered as "[h]:mm") and text Box2 = finish time (entered as "[h]:mm"). I would like textBox3 to display the difference between the start time and finish time as a general number!
For example
Start time: 21:00
Finish time: 06:30
Hours worked: 9.50
Start time: 12:30
Finish time: 23:00
Hours worked: 10.50
I need a sum function in A1 of a "Total"-sheet that totals cell A1 in every sheet with a certain color on the tab. The number of sheets can vary from time to time.
Any idea about a dynamic sum function that will do this, in combination with VBA?
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...)
I also have numerous set time periods that i'm interested in.. (about a dozen or so)
for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00
What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed.
So if my timestamps were
Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2
and if my timestamps were
Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps.
and if my timestamps were:
Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!
is there a way to have the number formatting in Text() variable? For instance, I want to reference a cell that can change between $1.00 and $10,000,000.00.
Let's say that in A3, the number turns out to be $10,000.00.
When I put in text(a3,"$0.00") it spits out $10000.00. But if I go the other route and go text(a3,"$000,000,000) it displays "$000,010,000.00".
I have an excel sheet, in that excel sheet is a command button, once clicked the command button creates a Word document.
Im trying to get my VBA script to bold certain text that will be in the word document. But all my attempts to bold certain cell contents have failed.
Im not getting any errors from the editor, its just not bolding certain text Ive told it too when the word document gets created.
I have included my script below (And Ive tried the following methods already)
1. ActiveCell.Font.Bold & "This Is Function One, This Text Will Appear Bold" & wks.Cells(ActiveCell.Row, 2) & _
2. "This is Function Two, This Text Will Appear Bold" & wks.Cells(ActiveCell.Row, 2).Font.Bold
3. "This is Function Three, This Text Will Appear Bold" & wks.Cells(ActiveCell.Row, 2) & (ActiveCell.Font.Bold) & _
Sub TransferData()
'This macro transfers the data range to a Word Document
'
'Constants:
'docFullName = The full name of an already existing Word document
'
Is it possible to format a character in a string using vba?
For example if i had a string of GGGFF, and i wanted to make the G's appear as Green font and the F's as Red font.
I have a userform that has a time field on it and then the userform displays the time as a number.
Ex. I entered 11:00 AM in the Time of Contact field and the form displays it as 0.458333333333333.
I have a column of cells with compound if statements like this:
VB:
=If(And(N10,K10<>""),"X","")
I'm trying to conditionally format this column of cells so that if the cell changes from this formula it'll turn orange. I've tried different variations of the following:
VB:
=N10<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"
=mid(N10,1,len(N10))<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"
=left(n10,len(n10))<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"
Basically, I'm trying to do a string comparison of the formula contained within a cell (N10) to a string that is assembled on-the-fly. If they don't match, then the cell should fill orange. I think I'm most of the way there, but can't quite get it to work. While this is simple to do with VBA, I'm rebuilding a lot of the functionality of this sheet using the functions available through the Excel UI -- users always forget to enable macros and always save copies as xlsx.
I have a cell containing a post code (U.K). For example CF678JU however I need it to be formatted to show CF67 8JU. Is there a quick option of setting a formatting rule to add the space instead of me going into each cell and changing... (there's thousands of them!!!)
View 2 Replies View RelatedI wrote an excel program in Excel'03 for a dental office to manage state assistance patients and one of the table columns is the state assistance number. The problem is that the program defaults to a number format when it is entered instead of a text format, which is what I want. The issue is that state assistance numbers are always 8 digits and when it defaults to a number field the program drops the required preceeding zeros.
For example the number 00123456 will sometimes show up as 123456, which is wrong. It's like sometimes it will show up right and other times it gets a butterfly in its brain and deletes those zeros irritating the receptionist here. So, how do I get the table to either stay in text format or set up a number format that keeps the preceeding zeros?
I am converting minutes to [h]:mm (thank you DaveR (UK) for this part)
The formula bar shows:
1/1/1900 11:51:00
The cell contains:
35:51
Is there a way to convert the 35:51 to a string and not time?
(changing the cell format to general after the 35:51 is placed gives "1.49375" which is the time in its raw format)
Sub test_time_2_String()
Dim a As Variant
Dim b As Integer
Dim c As Variant
Dim d As String, e As String, F As String
Cells(10, 21).Activate ' Arbitrary cell
' Break it down to its smallest parts in order to watch the progression
I Dimmed d, e, & F as strings yet it still formats the cell as time as soon as it's pasted.
The ActiveCell.NumberFormat = "[h]:mm" line is there so a least I have 35:51 instead of 35:51:00. Neither General or Text formats worked.
I have a cell that gives a title and then a cell value:
="Design and Build "& if(B19>=0,B19,0)
The issue is, I need the B19 value it displays to be an integer. Right now it's giving me about 8 decimal places. I've tried formatting that cell and the cell it calls, but neither has any effect.
i have 2 listboxes with numbers if user has selected choice in both then i want to enable okbutton. i tried in okbutton's mousemove: if listbox1.selected = true AND listbox2.selected = true then okbutton.enabled=true.
View 3 Replies View RelatedWhat I am looking for is whether or not there havebeen at least three, uninterrupted true cells in that particular column. The time is based off of the last time in that string that the cell returns a true statement. For example: O-Data returns 12:46:00 PM because there aren't any true statements in any of the other three columns between 12:43:00 PM and 12:46:00 PM and there are three true statements in that column.
M-Data does not return a time at 12:42:00 PM, because at 12:39:00 PM there is a true statement returned in the L-Data column (This is what I mean by interrupting a string).
I have a formula in VB code that is like this
sheet1.cells(irow,asktimecol)=asktime
when i place the cursor over each element i get :
irow="9"
asktimecol="5"
asktime="13:30:00"
so it reads
sheet1.cells("9","5")="13:30:00"
but iget the the error
application defined or object defined error
this is saying it cant place the asktime to cell
I have to bring the time thru as string as it come thru as one
I need to separate a list of times from a telecommunications bill. I have 170,000 line of data.
The Times are represented as below.
eg
46 46 Sec
59 59 Sec
100 1 Min
159 1 min 59 sec
200 2 mins
502 5 mins 2 sec
1256 12 mins 56 sec
3456 34 min 56 sec.
How do I separate them to a decimalized per min value.
I am working with a somewhat lengthy worksheet with over 60,000 records. oOne of the columns within the worksheet is the "Account Number" column, which typically consists of 14 numerical characters, but can sometimes have additional or fewer characters. I need to set up a conditional format to pick up any account number that ends in an alphabetical value, (e.g. a through z). The account number is located in column G.
View 4 Replies View RelatedConvert the first letter of a string to a capital without altering the rest of the strings format.
example: the activecell contains the string a1st (lowercase a, superscript 1st)
how can i achieve this in VBA?
Additionally, is it also possible to convert all of a string to capitals except the superscript characters
I have this data & would like to extract the date in dd/mm/yy and the time stamp separately to use in some calcs.
Jan 10, 2014 4:47 AM
Jan 10, 2014 4:47 AM
Jan 14, 2014 4:30 PM
Jan 15, 2014 4:00 PM
Jan 16, 2014 5:01 PM
I have an ACD program that gives me a time in "mm:ss" format, or in ":ss" fomat. I have 2-3 problems with charting this data.
1 I am inheriting the production of charts from someone else, with old data being displayed by seconds only I am supposed to keep this display.
2 the data that is in mm:ss format is recognized as being in full date format, for example 2:14 is recognized as 2:14:00 AM instead of 2 minutes, 14 seconds.
3 the data that is in :ss, ie :44, is recognized as a string.
Any thoughts on how I can convert both cases to a integer seconds? I know for example that I could mid() the second condition, but I am stuck thinking about the first condition.
I can't adjust the way the time comes in, unfortunately. It is an auto-generated export of the data from a switch.
I am looking for a way to add the 123 plus 456 to get 579. I have had some ideas that almost work, but I cannot get rid of the ' .
View 11 Replies View RelatedI having a bit of trouble entering numbers in Excel, every time I try to enter a number it seems to divide it by 100. For example : - 1 entered in to a cell becomes 0.01, 100 becomes 1....etc. I've tried formatting the cells and had no luck, tried Tool>Options but not sure where to look.
View 2 Replies View Related