Excel 2007 :: Time Difference Calculations And Automatic Insertion Of Text In Blank Column
Jan 4, 2014
Please find attached a daily copy of a spreadsheet that is used to monitor train running times.
The columns on the left - "Serv No, Serv Start Date,Train No:, Scheduled Arrival, Scheduled Depart" are provided to us automatically and the underlying cell formatting/formula cannot be changed. This information is cut directly from a daily report sent to us the previous day. The cell formatting for the start date is custom - dd-mm-yyyy hh:mm.
The sheet is usually locked out with user access only to the left hand side to cut and paste train times in and the input columns on the right hand side. User has no requirement to adjust any details in the left had column apart from to add additional trains at the bottom is need be. I have left the loaded sheet blank and complete with all underlying cell formatting, conditional formatting and formulas intact for you to play with.
Both sheets essentially do the same thing so whatever formula changes are made to the empty sheet can be adapted for the loaded sheet.
For simplicity, I will focus on the empty sheet. What I am mucking around with is having the user input the train arrival time in the "Actual Arrival" column - (column F)
What I am looking at then making occur is the spreadsheet automatically filling the adjacent columns with the appropriate text and calculations.
To do this excel needs to compare column the number (cell input is text formatted) as a number with the arrival time in column G. It then needs to calculate if the train is Early (16 minutes or more before scheduled arrival), on time (+/- 15 minutes either side of scheduled arrival) or late (16 minutes or more after scheduled). It then needs to automatically place the letter "E", "O" or "L" in column K and the time differential in column L.
My problem is two fold.
Firstly, I cannot seem to make the spreadsheet automatically enter a letter into column K depending on the above conditions.
Secondly, I have had limited success in having the spreadsheet compare column J with column F and working out the differential. However, this only works if the time remains linear. IE only if the train runs on time or late. It gives an incorrect time differential if the train is early. I will attach this spreadsheet tomorrow as an add on to this post.
I am trying to calculate mandays starting from 1st December 2012 to 29th March 2013, and i want to count the days excluding the weekends, formula, i am trying NETWORKDAYS, i use MS2007.
I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.
i have a tab on the bottom that is labeled "Man hours 2013" i need to pull the information from this sheet and create a new tab that will pull the property name, how many weeks of service, and the total man hours for each property. i will then be adding a column were i enter the monthly price for this contract divided by the number of weeks we serviced to figure out our percentage of gain/loss.
I am building a country ranking model which ranks several different macroeconomic indicators and applies a weighting to each of the indicators. I have an overall ranking column which is an average of all the ranks. The problem is whenever I change any of the individual indicator's weighting it obviously affects the country's overall ranking. Is there a way that the model can automatically "re-sort" the overall rankings without me having to manually click on the sort by smallest to largest filter each time?
I am working on a spreadsheet that keeps track of upcoming contract and service renewals. Within the spreadsheet there is a column named "Status"; using a formula based on the renewal and engagement dates this column will automatically state one of the following -
"Contract Status OK" "Up for Renewal" "Renewal Due"
When I open the the file I would like excel to email 2 specific email addresses. The email should only contain data from the rows that state "up for renewal"; this email will act as trigger for my team to engage with the suppliers in question.
The above basically checks for a number, and if fails (there could be #N/A, #DIV/0!, text, or Number-stored-as-text in any cell here), it checks for text. (the 65000 is arbitrary; we will never have more than a few thousand rows)
However this is not reliable--the main problem seems to be number-stored-as-text. And it's not possible for me to do the Paste-Special Multiply *1 trick to convert the numbers because this data is dynamically generated from a database--the user would have to do that every time the sheet opens.
Is there not some formula that simply says: "Give me the last non-blank cell after row 4 in column E, regardless of what type of data"? (Excel 2007)
I manage a team in a call centre and deal with lots of reports on excel.
I am dealing with a report on a daily basis that i need to use in order to calculate the total idle time that each of agents have. There are roughly 170 agents.
After I have formatted the cells to hh:mm:ss, the obvious thing to do would be : =(B1-A1)
Is there a way to write a macro (or anything) in order to reapply this to the report on a daily basis? Bearing in mind that this has to be done daily for 170 individual reports. They work a 8am - 5pm shift. So there is an entry every time they make a call.
Scenario - I started doodling with buying a property to let and then started thinking about adding the "shortfall" between the required bond payment and the income generated by the property back onto the loan each month as the shortfall arises. Let's assume that the financial institution will "pay" this shortfall from a previously agreed upon additional amount available on the property - understand?
While there is a shortfall between the income generated by the property and the bond payment, insurance, taxes etc., the outstanding capital will obviously increase as each new month arises and each specific shortfall is added onto the outstanding capital (loan at the bank).
Once the shortfall turns into surplusses, the surplusses are in turn paid into the account until the bond is paid in full.
With a normal, say 15 year, bond, the interest payments and principal payments of the constant mortgage payment (assume fixed interest rate) nicely works out to a balance of 0 in month 180, but not in the scenario I describe.
What happens in the scenario I described above is that, if I keep the payment constant (=PMT(and the rest - calculated on the original term and the original investment) each month's deficit is added to the outstanding capital but the payment stays constant. I want this payment to remain constant throughout the bond.........
Have 2 columns with time values and the third showing the time difference ( no Problems). what to hide the row if the time diff is > 2 seconds? (problem) What would be the best why to do this
{Sub TimeDiff() Dim i As Integer Dim timevalue As Date timevalue = "00:00.20" Application. ScreenUpdating = False With ActiveWorkbook. Sheets("Racing") For i = 4 To . Range("M1") - 1 If .Range("P" & i) > timevalue And Rows(i).EntireRow.Hidden = False And .Range("P" & i) <> "" Then Rows(i).EntireRow.Hidden = True End If Next i End With Application.ScreenUpdating = True End Sub
I have a Workbook with about 20 Worksheets, for each sheet I have a Selection Change Event that simply performs the Following:
MainSheet1. Range(“Z1”) = Now
1 Cell on every Sheet then is pointed to this cell via =Main!Z1. Problem is that when this is preformed Each Row from the Other In-Active Sheets and sometimes a near-by Button will Appear on my Active Sheet. I cannot select the parts of the sheets that appear and when I scroll down till they are out of view and then scroll back up they are gone until I select another cell and it happens again. I have tried in the Z1 cell to just put =NOW() and Make Calculations Manual and then with the selection change to call MainSheet1.Calculate or Workbook.Calculate but this has the very same effect and the other sheets bleed onto or ghost onto my Active Sheet. So far the only way I’ve been able to get around this is:
If Application. ScreenUpdating Then Application.ScreenUpdating = False MainSheet1.Range(“Z1”) = Now Application.ScreenUpdating = True Else MainSheet1.Range(“Z1”) = Now End If...............
I need to insert text at the curson position in a text box on a VBA form. I know how to make the button not take the focus; I just need to know how to pass the cursor a timestamp.
I have a sheet that I need to turn on auto calculation when selected. The problem I need to turn on auto calculation as well as sort another sheet "test" I do not know how to as the other portion of the code. attached will not work because it just runs in circles.
Private Sub Worksheet_Activate() 'THIS IS A SEPARATE SHEET THAN WHAT I NEED TO SORT FROM Application.Calculation = xlCalculationAutomatic
Sheets("6180").Select Application.Run "'BILLET-SLATE P414D working copy.xls'!Sortbypeprorprd"
I am trying to figure a way to search for a cell that has a specific date and time range. There are several cell titles pending on the activity. I want to find a cell that has a time ** 7:30-15:30 , 15:31-17:30, 17:31-20:30. The end result is to calculate the activity between those time periods based on the data cells.
Example
If the date searched time field ** the activity ranges is 1635 I need to split the time and credit the activity time in the 730-1530 time and the rest on the 15:31-17:30 time
I have been able to do it on a single labor group based on time alone, but when I try to add the DATE to it my numbers go null. Eventually i will need to add 11 labor groups daily for weeks at a time .
The full formula is quite long as this pick up the data off over 300 work sheets. (there's probably an easier way to do this aswell) When I copy or drag this to another cell with on the same worksheet it gives me the correct formular with the correct cells changes as I want. However this formular does not work. I have auto calculate on and I manually press enter on the cells and still nothing. If I change the following for the entire formula (about 8min to do each cell) it works
I'm using excel 2000 and I just noticed something and am unsure how to change. I guess early on my building of a workbook I accidentally left calculation to manual. I would like to change calculations to automatic on permanent basis. I keep changing it and it changes back to manual after I close then reopen the workbook. I would prefer to change permanently to automatic....Any ideas would be a bonus. On all new workbooks I can save in automatic but I cannot change from manual to automatic.
I have a need to insert formulas into a column using vba. I need to skip a row, when inserting the formulas, so a simple drag using vba would not work without some modification.
Here is the formula, that I need to insert... into column "BJ"
Example Cell BI11...=SUM(BH11-BH10)
Cell BI13...=SUM(BH13-BH12), the next cell needing a formula would be .. Cell BI15...
The formula would be inserted, based on the last row used in column "C".
click on cell A4 -right click - insert cell- shift to right, so figure 2 moves to C4 pushing the yellow cell to D4 right?
what am trying to do is to make column c to be frozen or locked or whatever you want to call it, so if I insert a cell in A4 shifting to the right, the value in B4 jumps to D4 without shifting the yellow cell
I've been using this code to make menus for all my Excel sheets since NateO made it back in 2003.
- The workbook has 370 sheets, 365 of them are one day per month with the tab named ddmmmyy. - The Main Menu in this file puts the months Jan, Feb, Mar, etc in cells A3:L3. - In the single cells under each month it put the month day tab name (ddmmmyy) and hyperlinks it to the sheet tab.
I would like to do two new things. 1. Put the name of the month in everyother cell in row 2, so Jan would be in A2, Feb in C2 and Mar in E2, etc. 2. Put the day of the week for each date in the cell to the right of the date. So the column heading would be Jan in A2, 01Jan08 in A3 and Tue in B3.
I thought I could make another macro and just insert the rows after the Main Menu was built and then VBA copy/paste TEXT(cell,"ddd") down the ranges, but thought there might be a better idea out there...
Sub Workbook_TOC() Dim ws As Worksheet Dim wsNw As Worksheet Dim z As Integer
I am running a text comparison between customer names in 2 systems and although I can identify where they dont match I would like a bit more information as to what the differences may be:
See below sample data - 1 system may have Mr/Ms and middle initials whereas the other simply has the name is it possible to write a formula that will look at the cell and when it does not match identify the extra characters. Or if it totally doesnt match then just state incorrect.
The below piece of code carries out a vlookup on a defined cells value and produces a result in sheet one, however if the column index number in sheet 2 (Database) is empty the result 00/01/1900 is produced.
I'm not sure how to say leave the result blank if the column index number is blank.
I have several sheets with about 250,000 rows per sheet.
But, even I sort by Column A, there are STILL hundreds or rows that are total blank interspersed down the page . . . I can't autofilter for blanks because there is too much data . .
I have an excel file with many worksheets. I want to get a pop up message providing definitions of different subjects when they are entered from a drop down list in a specific range within a column to improve the users understanding of the subjects.
Worksheet 1 Range (where I want the pop up message to be valid): J85:J385 Subjects from drop down list in specified range: "x,y,z" Pop up message: "Definitions of x,y,z"
Do I have to make a new module, or write the macro in the selected worksheet? What should be the settings of the macro (general, worksheet, declarations etc.)
How can I create a Makro that looks for values (e.g. values > 90) and that then generates a list with: all the values greater than 90, the row names and column names of the values?
I am pretty sure I will need a Makro for this, but how to create it. I am using excel 2010.
I have attached a file to give you an example. The red table is the table with the values. The blue table is what I want to be generated automatically. In the example I did it manually.
I am making a time sheet for work. It should be very simple but cant get it to work. I want time in, time out, lunch column, and Total. I don't want a lunch in and a lunch out though just a section where I can put my time in :30 minuets :45 or what ever. I also want it to work. on a 12 hour clock AM/PM .
Here are my column's B2 Is Time In C2 is Time out D2 is Lunch time ( decimal ? ) E2 is the TOTAL with lunch deducted.
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.
