I am trying to extract times from a work schedule to automatically generate a 'time in' / 'time out' chart. Been trying to use the lookup formula amongst others with little success.
See example template attached...Example Sheet.xlsx
Combing these two formulas. I have a work schedule spreadsheet. If the employee is Off I want the value to = 0 (zero hours). But if the cell has a start time I need it to calculate those hours. I know both of the formulas work individually. But I need them to work together.
Formula for when cell says OFF:
=IF(C11="Off",0)
And the second
Formula to calculate hours (based on start time/finish time is the same for all employees)
=($X$4-C11+($X$4<C11))*24
I have made a work schedule for my local business and have set up a series of formulas that will fill out time cards that I could print out directly onto the paper time cards. The formulas that I have work except that if there are two subsequent entries that later will not return a value and result in an error.
If you could take a look at it that would be awesome. To use it you just need to type a name into the name column and a work time into the time column for that day. then in the other sheets( one for each worker ) it will set up the time card. The the error happens on Thursday, when Bob has an entry right after Fred. Then on Bobs sheet it gives me a #N/A.
In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
I have been trying to achieve the following with formulas but have not been successful. I am making a simple schedule, and I am trying to validate the cells so that you can not schedule someone for multiple tasks during the same time range. In the example that I attached, Harry is diswashing from 1:00 - 4:00 but is also scheduled to paint from 2:00 - 5:00 (this should not be allowed). How can I achieve this with code?
View 7 Replies View RelatedEach row of the spreadsheet from a6:a25 is the planned production of one cellbuilder.
From this sheet I need to produce a work schedule of tasks to be performed by date.
Detailing, batch, cellbuilder number,weather 1st or 2nd graft etc, and the date available (for picking)
I would like the option to choose a daily or weekly schedule.
Is this possible ? and how would I even start to go about it. I just can't visualise how to do it.
I am working on a monthly employee work schedule that has 2 stores. All employees work at both stores and the boss wants 2 separate schedules.
I have attached what I have started, which at the moment I am stuck.
I would like to have the Row 5 total hours to always reflect (subtract) a 30 minute lunch. I know it would be easier to have another column, but the boss wants it this way,
I also need the total hours in column H to reflect the total of each day (with the subtracted lunch break). In this column I am having the figures turn red if over 40 hours and green if it under 40 hours.
With having 2 stores with the employees working at both, I need to somehow have the hours of the employees add up from one store to the other (a running total, if you will).
Lastly, if I have an employee not scheduled for a day or need to put other data such as, VAC, HOL or blank cell etc in a cell, how can I get this to work and not have the formula go nuts when it is not in a time format.
I am working on an easy to read worksheet for my employees. i have a multi-page schedule that i would like to display on one page, i can make this work using the IF function, how ever i would need to display names not the time they work, how to make this work, i have attached and example sheet of sheet 1 and sheet 2. I am trying to take sheet 2 into sheet three to display names, if they are off to display nothing.
View 1 Replies View RelatedI am attempting to create a rotating weekend work schedule in Excel to make life easier. There are 30 people who rotate Saturday's and Sunday's every month. Technically, they are only supposed to work 2 weekends a month, and am attempting a way to create this in Excel to make it more fair to the group instead of just picking and choosing which weekend these employees work, I would like an Excel program to do this for me.
View 1 Replies View RelatedI developed a 14-day work schedule and I assigned each different job position a number. The different job positions are numbered 1-6 and are as follows: #1=5:30am-1:30pm, #2=6:00am-2:00pm, #3=9:30am-1:30pm, #4=12:00pm-8:00pm, #5=1:00pm-8:00pm & #6=6:00pm-8:00pm. Numbers 1,2,4,5 clock-out for a 30 minute lunch break, while the other numbers do not.
My goal is to insert the numbers 1-6 into the spreadsheet throughout the 14 days for each employee, and have Excel calculate the total number of hours for each employee in the far right column. I would also like "V" & "H" to equal 7.5 hours. This would save a lot of time instead of going through and adding up the hours with a calculator
Maybe it is the layout but I was trying to keep it simple and just put hours into a cell for each employee.
I have 2 employees per day, one in the morning and one in the evening. How to get excel to add the hours for each employee at the bottom per month. Simple right?
Here is an example of what I thought would be so easy (first week in excel spreadsheet format)
sun mon tue wed thu fri sat
emp1 5 emp3 6 emp5 5 emp1 4 emp3 5 emp5 5 emp1 6
emp2 5 emp4 5 emp6 5 emp2 5 emp4 5 emp6 6 emp2 6
Is there a way to associate cells to look at values for each employee and add all the cells for the month?
What am I doing wrong here. Some times if I do a =if( ) formula it doesn't work. For example, look at the attached picture. Cell K63 should say "End of Run". But it doesn't, What gives?
Also I have had before where i do =IF(X62>$O$2,"End of Run", 0) (and $O$2 =81) and the cell when X62 is 81, not greater than. I know I can to >=, but thats not what I am doing.
I have one Excell Book with two work sheets. The 1st sheet (Sheet Name : Data ) contains the data... and in the 2nd sheet (Sheet Name : Rekey) i have a specific form in Rekey sheet and some data in Data sheet. Now i have to copy Rekey sheet into multiple times .... with different sheet names... and the sheet name are in Data sheet from cell A2 to end.....
For e.g. in Data Sheet cell A2 contins work1 and cell A3 contains work2 and A4 contains work3..... and so on....
Rekey sheet contains some form....
I need to paste Rekey sheet multiple times with sheet name work1, work2, work3..... and so on....
I am creating a 'HelpDesk Issue Logger' and I am trying to calculate the Network Days and Network Hours between two dates with times. I want to know how many business days and hours are between the two days to give me a TURE 'Response Time.'
I have been to several sites and forums looking for the answer, but I have not been able to find a solutions that works for me. Please find attached a 'stripped' down version of my project.
Variables:
- 'Date Received' (H11)
- 'Date Actioned.' (I11)
-'Response Time' (K11)
Constants:
Work Days = Monday to Friday
Work Hrs = 8:30 AM to 5:30 PM (no lunch break)
Public Holidays = (AC13:AC30)
Formats:
Date Received: dd/mm/yyyy hh:mm AM/PM
Date Actioned: dd/mm/yyyy hh:mm AM/PM
Response Time: d - hh:mm
If there is someone out there wiling to put me out of my missery with this one, you will have a friend for life.
formula to work out a variance between two times
Using the 24hr time format in cell a1 i have a start time of 10:43 and in cell b1
i have an estimated time i think a job should take in this case 30 minutes and in cell c1 i have the actual time that job was finished in this case 11:07 and in cell d1 i have a variance between the two times which in this case would be saving me 6 minutes
I'm looking for the reason why the following use of the INDEX function returns a cell address [ie the formula evaluates SUM($C$2:C3)]-
View 5 Replies View RelatedSay employee John Doe has a salary X. Giving him a salary raise Y (euro/dollar) induces a pension premium to be paid by the employer, on that amount.
The premium is (fixed) 17% of the part above a (fixed) treshold value of 3.750. On the part of the raise below the 3.750, the pension premium is (fixed) 4,35%.
E.g. having a salary of 3.600, with a raise of 400.
4,35% of (3.750 - 3.600 = 150) equals 6,525
And 17% of the remaining 250 equals 42,500
So the total premium is 49,025
But, I want to give a raise with a total cost of no more than 400. So, I use goal seek to find a raise that, together with te pension premiums equals 400.
In the example, with 3.600 as a salary to start with, that raise would be 358,10 Because the part below 3.750 stays the same and induces a premium of 4,35% of 150, being 6,525 and the remaining part is 208,10 at 17% is 35,377.
Proof: 358,10 + 6,525 + 35,377 = 400
The question is: is there a possibility to have a formula that calculates the (e.g.) 358,10 based on a salary to start with (e.g. 3.600), an aimed total cost (e.g. 400) and the fixed constants 3.750, 17% and 4,35%?
I need to determine federal tax withholding from gross pay.
If gross amount (for S-0 = single no dependents) is
Over.................But NOT Over............Then Subtract.........And multiply
$0.00...............$195.00.....................$5 1.......................10%
$195.00............$645.00.....................$99 .......................15%
$645.00............$1,482.00..................$351 ......................20%
$1,482.00..........$3,131.00..................$447 ......................25%
$3,131.00..........$6,763.00..................$849 .....................30%
If gross is $400 then what are the total taxes taken out? Could someone please provide a formula?
I tried this one: =IF(M10
I'm looking for an answer about how I can show the numbers underlying a
nested IF function. The formula works fine, but I need to be able to show
how I arrived at the numbers that I did. Here is an example of one of the formulas:
=IF(B9=1,(B4*Data!G5*Data!F5),IF(Calculations!B10=2,(Calculations!B5*Data!G5*Data!F5),IF(Calculations!B11=3,(Calculations!B6*Data!G5*Data!F5),0)))
Instead of the reference to the cell, I would like the formula that the cell represents. I know that I could go through individually and pull each piece apart with control F9, but since there are numerous cells and because the data changes every time I create a new budget, it would help if I could have some way to display it all with one action.
I have thousands of data entries (each in their own row, all in one column) and need to separate the basic info. In the example below, say you want to isolate the Town names by entering a formula that can produce a TRUE or FALSE result. Is there a way to use the FIND function and a pool of terms to isolate cells that contain excess data (i.e.- produce a FALSE result for cells containing any words from a term pool like : "Police, Park, Power, Water, Community, Bakery, Grocery, Field, Garden, Post Office, School" etc... ) ?
Pic of sample spreadsheet with desired result:
Screen Shot 2014-03-18 at 3.14.09 AM.png
example as text:
A2 = Springfield
A3 = Springfield Police Department
A4 = Springfield Community College
A5 = Springfield Park Of Recreation
A6 = Des Moines
A7 = Des Moines Power & Water Company
A8 = Des Moines Sunrise Bakery
A9 = Des Moines Post Office
A10 = Des Moines Grocery Store
A11 = Lancaster
A12 = Lancaster Baseball Field
A13 = Lancaster Botanical Gardens
I have repetitive task of copying and pasting. Macro to sort some data.
I have time values (sorted in order) in Column A and a value in Column B.
Sheet1 Â AB1TimeValue
200:00:002300:00:002400:00:001500:00:002601:00:004701:00:002802:00:001902:00:0021002:00:0031102:00:0041202:00:0011302:00:002
I need separate the time each hour of data into columns. So the above would become
Sheet1 Â ABCDEF1TimeValueTimeValueTimeValue
200:00:00201:00:00402:00:001300:00:00201:00:00202:00:002400:00:001Â Â 02:00:003500:00:002Â Â 02:00:0046Â Â Â Â 02:00:0017Â Â Â Â 02:00:002
There are a different number of rows of data for each hour and sometimes there might not be any data for a specific hour.
I have created an online survey, and people choose ten words (skills) out of 24 possible. Please see screenshot. I would like a formula that does this in layman's terms: "If I see the word "Cooperation" in the source cell, then I'll put "03b" into the target cell; but if I see "Managing" in the source cell instead, I'll put "21a" in the target cell, etc."
I've tried a few IF / Then statements, with no success. Screenshot shows the source cell upper right, and the ten target cells below left with two rows of sample results. I'm guessing the same formula can be in each of the 10 target cells.
I have a column of over 500 replies to a survey. I want to create a pie chart showing the average ages of the respondee to that that question, broken down into increments of 5 years (this was the question -- how old are you) and reported as a percentage.
View 9 Replies View Relatedi need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies View RelatedIn my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
I need a formula in excel to convert start times and end times and minus out lunch time taken and then give total hours worked. For instance,
Column D Column E Column F Column G
Start End Lunch Total Hours Worked
10:01AM 7:08PM 1:01 (formula to convert hours worked)
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook
the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
i have attached the code in notepad ...
In column J, Cad Hrs. How to make to display (0), instead of (-0,02) when I just do other work than Cad Work? I need to separate type of work according to price hour, overtime, and so on.
View 2 Replies View RelatedIs there a way to take the values between C2 & D2 and have them automatically post in column F ?
View 2 Replies View Related