Time To Stay As Text
May 30, 2008
I have a spreadsheet that tracks employee stats. Most of these stats are amounts of TIME. These times are being pasted from another source and the problem I'm having is that as soon as excel sees "1:02:03" it puts it in TIME format. The problem I have with that is later on I need it in text format because a lot of the other times are in text already. (the program the info is coming from will use ":59:59" and not have a 0, so I have compensated for that later) So how can I paste 1:01:01 and not have it saved as TIME? I cant CONVERT it to text, I need it to be text from the start.
View 9 Replies
ADVERTISEMENT
Nov 13, 2013
I have text boxes for additional information. Is it possible to make text boxes to stay in place so that if I or someone else deletes a column the text boxes would not shrink and move to the left?
View 2 Replies
View Related
Oct 4, 2009
I have a table with a range A1:C6
If B1 is “hello” I want B1:B6 background to change to green, and text to stay black
But if B3 is “goodbye” I want this to override the above and make B1:B6 background white and text colour of B1:B2 and B4:B6 white (so the text in these cells seems to disappear).
View 9 Replies
View Related
Jul 19, 2014
I am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.
THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.
I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.
View 7 Replies
View Related
Oct 11, 2008
In Cell O4 I have a number 35 that number never changes.
Now in Cell C40 I have another number that is a formula. the formula gives me a total of how many points are left.
In C42 I want the total to either been 35 or subtract the negative number from 35, the points can never go over 35.
Any number that is a positive or 0 will equal 35, if a negative number subtract from 35.
View 9 Replies
View Related
Jan 9, 2009
I have Excel 2008 for Mac (vers 12.0). On a basic spreadsheet, the first column is for date which I have formatted so I can type in 15/8/9 and it appears as 15-Aug-09.
However, each time I open Excel after having Quit it, I have to reformat the cells in this column again as they revert to converting my date input with something like 39,123.
My other columns are formatted to currency and are always okay.
View 10 Replies
View Related
Oct 29, 2007
I set the scroll area on a worksheet and saved the file. The next time I open the worksheet the scroll area is no longer set.
View 7 Replies
View Related
Jul 25, 2014
1. I have a userform I created that has a text box that pulls it's data from one of the spreadsheets tab. The userform also has 5 buttons I shall call Tab1, Tab2, Tab3, Done, Cancel.
2. The first thing a user will do is make a selection from the text box.
3. The User will then select one of the "Tab" buttons.
Below is my code. What happens is that no matter what button I click (Tab1, Tab2, Tab3) it ends up on Tab3 because it is the last transfersheet line of the done_click() sub routine. How do I get it to stay on the Tab I select?
+++++
Private Sub cancel_Click()
Unload UserForm
End Sub
+++++
Private Sub done_Click()
Dim transferworksheet As Worksheet
Set transferworksheet = Worksheets("Tab1")
transferworksheet.Cells(5, 7).Value = Me.ListBox1.Value
[Code] ..........
View 4 Replies
View Related
May 16, 2014
In Spreadsheet A Cell H8 is entered as Item Description.
In Spreadsheet B Cell H8 is also entered as Item Description, but if the Item Description in Spreadsheet A has been changed, I need Spreadsheet B to reflect that change, if not it should stay as Item Description.
View 5 Replies
View Related
Oct 2, 2008
I have a formula:
=INDEX('Part Info'!E1:E300,MATCH(1,('Part Info'!A1:A300=A1)*('Part Info'!B1:B300=B1),0))
I want the E1:E300 to stay E1:E300 when I drag it down but I want the A1 for example to change to A2, A3, A4 and etc.
View 2 Replies
View Related
Nov 23, 2008
i have this formula which works well...
View 8 Replies
View Related
Jan 28, 2013
How do I lock a column header so that it will always stay at the top as I scroll down the page?
View 4 Replies
View Related
Apr 1, 2014
I am running the below macro which work fine ! However after running the macro, I do not stay in the cell I was!! How to stay in the selected cell?
HTML Code:Â
Sub INSERT()
ActiveSheet.Unprotect Password:="P@ssw0r!"
ActiveCell.EntireRow.INSERT
Range("FORMULA1,FORMULA2,FORMULA3,FORMULA4").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="P@ssw0r!", AllowFormattingCells:=True
End Sub
View 1 Replies
View Related
Oct 7, 2008
I have two columns of data
1st - Multiple physician names (may be duplicate as data set is Emergency Room encounters)
2nd - Denotes the length of stay in minutes for each patient encounter
I want to develop a formula to calculate the median length of stay (off 2nd col) for every physician a select in a drop down.
View 4 Replies
View Related
Dec 12, 2011
Every time I copy and paste cells in a specific workbook it changes the reference style to R1C1. Setting it back in the options, saving the document, closing Excel then reopening brings it back as A1 referencing but copy and paste will again return it to R1C1.
I've checked for VB code and there's nothing there either.
View 1 Replies
View Related
Jan 29, 2012
While using Excel 2003, I am aware of "freeze plane" and "split worksheet". The freeze plane comes in handy if I want to the first few rows to remain stationary and displayed while I can scroll down to the rows below. Similarly, the split worksheet comes in useful if I want to have the first few columns remain in place while I can maneuver to the right to see the other columns.
*** THE QUESTION IS: how can I get cells A1 to F20 to remain displayed and unmoved even if I choose to move the active cell around anywhere between G1 to K9999
View 9 Replies
View Related
Mar 19, 2008
I have a cell (H27) which has a (sum) total in and have attached a NAME FRED.
This is fine as long as the client doesn't add a row above it.
If they do when I re-open the sheet the NAME doesn't stay attached to the cell (now H28)
Is there a way to keep the NAME attached to the cell regardless where it is?
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("$H$27").Name = "Fred"
End Sub
View 9 Replies
View Related
Apr 22, 2006
I have an excel spreadsheet that shows every other row in a pale blue. When I do data sorts, the rows loose their order. Is there a way to keep the row color in every other row and still keep it that way after the data is sorted?
View 2 Replies
View Related
May 11, 2009
I want code to move the cursor to A788
But keep A788 in the top left corner on screen.
Is there easy code to help me accomplish this?
Also, Is there code that will take you to the first cell it finds closest to today's date.
Dates are in Column B.g
View 9 Replies
View Related
Jun 23, 2007
Imagine you have only one column - column A. In column A you have 500,000 + rows with text like the following:
!magnus
snooze'
sleepy/
act noun
act verb
Today&tomorrow
*bialgebras
/dev/ null
dev
ull
1970;
Dwight Schrute
and your goal is to figure out a way to simultaneously transform the above rows into:
snooze
sleepy
act
act
Todaytomorrow
bialgebras
devnull
devnull
1970
DwightSchrute
I know I can use Filter and filter out characters like &'!/; but that takes too long because I have a lot of lists to go through with thousands of entries. There must be a macro that will close spaces between words when only one space exists, but eliminate every word if 2 or more spaces exist between words - as well as eliminate unwanted characters like the ones I described above. I know I could use substitute and eliminate the spaces between words =substitute(a1," ","") but then I'd have to filter each and every instance of unwanted character.
View 6 Replies
View Related
Mar 19, 2014
How would I fix my formulas from not working when I filter other columns? It is a simple =A1/A3 cell and this calculation is down the full column. When I filter any column, the calculation does not stay with the assigned cells.
View 2 Replies
View Related
Aug 21, 2014
I have a list or log that is updated by a number of people on a sharepoint file. The list consists essentially of 2 columns - lets call them Location and Date.
The Locations are populated from a pulldown list but can be repeated a number of times throughout the list. I have the list set up using Table Formatting so the range updates dynamically
Example:
Location Date
Site1 8/1
Site2 8/2
Site3 8/3
Site4 8/5
Site2 8/6
Site2 8/7
Site3 8/8
As this list gets items added to it I want to populate a summary table on another sheet showing the dates each site was visited like this...
____8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 8/9
Site1 X
Site2 -----X-------------X---X
Site3 -------X
Site4 --------------X
(Had to add dashes above to get the X's spaced out properly)
I'm competent with lookups and such but I imagine this needs an array formula or some index/match combination which I'm a little weak on.
The solution should also not require any updating as the source list is updated periodically.
View 4 Replies
View Related
Aug 18, 2009
I have two columns D and E, the header takes up rows 1-4 so the first available row is row 5. The D column is going to have numbers added daily, the E column will show the cumlative total. How can I make the cumlative total stay and be updated with each new entry in column D?
C D E
Name daily cumlative
dave 20 20 Row #5
Jim 30 30
Tim 11 11
Sara 6 6
Say these are the entries for day one, I need to be able to make new entries in the same D column on the next day and have it update the running total in E?? I cant figure the darn thing out.
View 12 Replies
View Related
Dec 9, 2009
I have approx 500 workbooks which contain custoemr details and I need to extract certain info (postcode, name, contact, phone number etc) and place it all on 1 spreadsheet. I was originally going to source data from unopen workbooks, but have realized I only need to do this task once, and I can maintain it from then on...
I have created a spreadsheet (attached) which uses INDIRECT formulas to retrieve the data. When I type in the file name in column A, Columns B to K fill themselves in. What I want to know is:
Once I open say, 10 Workbooks, TYPE the name in manually and all the other cells have filled themselves, I want to somehow KEEP the data there, rather than it return a #REF error when I close the workbooks to open the next batch.
Also, if you look at my workbook, to the far right is a range of data which I use in my INDIRECT formulas. Once I have filled all the cells with the relevant data, I want to be able to Filter my spreadsheet by Date Ascending. BUT I cant seem to do it because it includes my INDIRECT range in the filter and mess's up the formulas then I have tried Locking & protecting workbook, with Autofilter & select cells etc TICKED for users to use, but it still tells me to unlock workbook when I try to filter.
View 5 Replies
View Related
Jul 10, 2013
I've created an excel workbook with multiple sheets. One of the sheets is the "Master Sheet" and I used the formula =INDIRECT("'Master Sheet'!A"&ROW(A2)) so that columns A and B on every sheet match exactly what I input into the A and B columns on the Master Sheet. I used this formula because it would allow me to insert rows on the Master Sheet and have that reflected in the other sheets.
However, I've realized that this means the data I am inputting on the other sheets gets misaligned because if I make a change on the Master Sheet it the A and B columns on the other sheets, but all the rest of the columns on the other sheets stay where they are. How to make the rows shift up and down together on the other sheets.
View 3 Replies
View Related
Jan 29, 2014
Does someome know a macro to simulate mouse movement to stay idle to prevent communcator (Lync) from turning off? I need this to run for 2 hours only.
View 1 Replies
View Related
May 7, 2009
Is there any way to have a web query stay connected and get live data from a site while still being able to use excel at the same time?
Basically the site has information that changes by the second and i need to keep a running record of it, and when it hits the variables needed i want to be able to trigger a set of code.
View 9 Replies
View Related
Mar 18, 2014
I am my excel worksheet (excel 2010) I have one cell that changes every day (number). I want this number to open my htm document and replace the same number in a string in the htm and save/close this.
An example:
My htm document is located at C:/ and named XX.htm
The number I want from excel is in cell A1 in sheet1, and the worksheet is located in D:/ named yy.xlsx
And the text(number) I want to replace is in the following string in the htm document, in this string it is 72, next day it can be 30:
src="Bilder/72.png"
View 8 Replies
View Related
Dec 20, 2007
I have a macro that is designed to paste a row of formulas into each Subtotal line on a 30,000+ line spreadsheet. I have specified the formulas that I want to paste in the named range "formulas". The macro below worked fine for about 3000 lines, then threw the error Run-Time Error 1004: Paste Method of Worksheet Class Failed on teh line ActiveSheet.Paste. I thought it had just bogged down my computer, so I added teh ScreenUpdating and Calculation lines, but they made no difference. I also tried reducing the range to just a couple thousand lines, which also made no difference. I also have found some suggestions on this forum and others for this error.
In addition to it working fine for 3000 lines, then failing, there are a couple other weird things. One is that on the line in the spreadsheet that threw the error, the formulas were pasted in up to column AX, leaving columns AY through CR blank. The second odd thing is that this exact same macro still works on my sample data spreadsheet (which only has around 50 records).
Sub FormatTotalRows()
Dim rCell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Workbooks("latest.xls").Activate
Range("formulas").Select
Selection.Copy
For Each rCell In Range("A3040:A5000")
If Len(rCell) > 0 Then
rCell.Activate
ActiveCell.Offset(0, 2).Select
ActiveSheet.Paste
End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
View 2 Replies
View Related
Mar 6, 2013
How to freeze/split panes so the colored boxes on the left stay fixed in place while the other boxes on the right scroll up and down? I'm figuring out the right combo of freeze and split panes.
View 1 Replies
View Related
