Vba Detect When A Specific Set Of Cells Gets An X Typed Into It And Calculate 1 Cell = 36.50 Hours
Oct 22, 2009
I'm working on a timetable for my boss and i'm trying figure out how to have vba detect when a specific set of cells gets an x typed into it and calculate 1 cell = 36.50 hours and input into the appropriate Sum column. The Sum Total is Column AR and i've put x's into some of the cells that will be containing them, i'm sure if i just get the basic code needed i will be able to implement it.
i have a timesheet that we are trying to use. the problem is the column that says shift diff. if an employee works after 6:30pm for 1and 1/2hr, he is entitled to shift hours. shift hours is between 6m and 8am.
As long as he works after 6.30pm but works for at least one and a half hour, he will get the shift.
if work, 9am to 7:30pm, and have break between 2-3pm, should have 1.5hrs shift and 9.5hrs total if work, 7:45pm-9:45pm, and have break between 8:30-9pm, total hrs work is 1.5 and shift hrs s/b 1.5hrs if work 3pm to 12am and have break between 7-8pm, total hrs work is 8 and shift hrs s/b 5hrs
I need to calculate the hrs between two date and time and deduct any time outwith the hrs of operation (09:00-19:00), if the date and time falls outwith these times. I have used the formula 24*IF(A2>G2,G2+1-A2,G2-A2), where G2 IS 21/04/2008 11:45 and A2 is 20/04/2008 00:22, but can't get it right.
I need a formula to calculate hours worked between specific times. In the worksheet I have, I need to calculate the hours worked between 5am and 10pm, the start time is in C7, and the finish time in D7 the formula need to be in H7, and another formula in I7 to calculate hours worked before 5am and after 10pm .
Is it possible to check the conditional state for a specific condition for a specific cell. For example a cell has 1 or more conditions.
A condition will toggle to a condition if true or false etc. The specifics is i use red (color 255) if a cell has not met a specific condition (of 1 or more conditions) So if the cell is mandatory to be populated, and is blank, it will be red, if not it will be something else.
But i want to loop all visible cells with conditional formatting, and where the cell has a condition when true will show interior color = to 255 return the cell address. To msg the user where data entry is still required etc.
I have a worksheet and in two of the cells I have text. In another cell I want to put a formula in so that if certain text is typed into each of the two cells a result will appear in the third cell.
Hazardous Smiths 60%
Non Hazardous Smiths 20%
Hazardous Browns 45%
Can I put a formula in and if so, what formula would it be?
I have a worksheet where I capture the date and time of each new entry in column A.
In the next column, it's counting up the number of hours since that entry was made. However, it's including Saturdays and Sundays in this count and I was wondering if there's a formula to calculate the number of hours excluding Saturdays and Sundays? I know that network days would count the number of working days from one date to the next, but really need hours. Is this possible?
Lunch is not paid. Holiday and vacation hours get calculated at the regular pay rate. Overtime is anything in excess of 8 hours per day and/or in excess of 40 hours per week and/or over 5 working days per week. Saturdays for most the employees will be overtime because it will be their 6th workday of the week; but it will be regular time for one employee as it will only be his 5th workday of the week.
For accounting and payroll purposes, we need the totals to display in both hour and decimal format.
So far, I have Lunch, Regular and Overtime hours figured out, but I still need to work with Saturday, Vacation and Holiday hours. Also, currently, the time in and out has to be typed in with the colon and AM or PM. Is there another way to input the info without having to type in those items? I'm trying to make it as user friendly as possible.
find a formula that will calculate the hours between the two below values but only take in to consideration the business hours (from 9 to 17) and exclude any weekends?
Is it possible to calculate hours if both the start and end time are in the same cell. Unfortunatly the developers of our scheduling program developed it so when you export the schedule to excel it shows Greg in A1 and 3:00pm-9:00pm in A2. I would like to have excel calculate the 6 hours so I can use it to auto generate another spread sheet I am making.
Problem: I want a macro to detect if a workbook is open so I can skip the workbook and move onto the next item. I do not want the user to be prompted in any way. I do not want the workbook to be shared. I simply want to pass over the workbook (but be aware I have passed it over).
--Long Version-- About forty team members have their own workbooks and at day’s end the team leader uses a macro to poll each team member’s workbook in turn to gather work results. When gathering these results the macro also needs to write some admin data to each workbook.
I am aware of the workbook sharing option in Excel, but I am wary of using it. It is not a show stopper if a team member’s workbook is already open and work is not gathered on a particular day – we’ll get it the next day. I just need to be able to trap the event and manage it. The central command I use to access a team member’s workbook is :
if there is a way (without using a macro) to calculate just a specific column or selected area in a spreadsheet. It is a colossal spreadsheet and takes a few hours to calculate even just one sheet. I know you can go through it hitting F2 then enter a lot but that is getting tedious!
How can I create a formula to add up how many cells in a column show a particular set of text? For instance; the column in my spreadsheet looks something like this:
MJR DML MJR RJG RJG MJR DML
I want to know how I can create a formula that will automatically tell me how many times "MJR" is listed; how many times "DML" is listed, and so on.
I am trying to pull titles out of a text string but its quite difficult because of how the data was originally inputted. I need titles that appear in the following different formats
" title" title/ title"
These titles were not inputted consistently and it could show up in the middle or at the beginning. I have attached a sample of the data that i am working with and a sample formula that i was trying to use to obtain my information. The formula that i have is a nested formula of find and mid formula that searches for the text within " ".
I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.
What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.
How can I detect on an excel workbook which cell have links(formula =) to other workbooks without have to put my cursor on top of each linked cell.
I have had a hard time finding this cells. I can see the links by choosing Edit then links, and excel will display list of links, but it does not specify which cells contain those links
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
I am designing a simple time card. Column D=time in, Column E=time out. My formula in column F to calculate total hours is =TEXT(E2-D2,"[h]hrs"). The result is not correct. Example: In at 9:30 out at 5:00 and the calculated total is -4 hours.
I have a code and I want to run this macro whan a specific cell change (which has sum formula) and this code also has some calculation. And I m not understanding to overcome this problem through Calculate event.
See the attached excel table. I need the cell E4 to keep adding the values typed in the cell D4 on daily basis.The cumulative shouldn't be changed if there is no value in the cell D4. E4 should accumulate and keep the totals typed in D4...
I was wondering if there would be a way to use vlookup and still be able to type text in the same cell. For example, if the vlookup gives me a result of 123. I want it to also pull from a different cell that I've typed in 321. So the resulting cell would show 321-123 or 321 123. I have attached a sample sheet. On sheet two where the 3 items are, I want it to display 321-123 for item 1, 321-234 for item 2, and 321-345 for item 3. The 321 would be dependant on what I type into B1 on the first sheet.
This is for employee time sheet and schedule. I have multiple employees work a specific shift on various days. I need the calculate the hours worked each day and then have them totaled at the end of the period. Here is and example:
Employee Monday Tuesday Wednesday Thursday Friday Saturday Sunday Total Hours Total Cost John Smith 10-5 12-6 9-2 5-10:30 3-8:30
How do I get it to calculate the difference in the same cell, convert it to hours, add up all the results and place it in the totals columns then calculate those totals by their pay rate to get my Labor cost?
I'm trying to create a work sheet with start and ending times with a 30 minute meal break, for the end of the week I would like to calculate those hours as a general number.
I would like to calculate the total hours between two dates using a unique formula and without inserting new columns. I have attached an example of the data I'm working with.
(I hope the ... is okay, couldn't figure out how to make the columns line up.)
I can use Sheet Two for the next part, but I don't know how to to get a total time per month for each seperate jeep. Most of the time, there will only be one record per month per jeep, like the second line above. But lines one and three are for the same jeep, and so I need the formula to add column E up only for those two lines.
