If you look at "B9" I have created a list of names and when I choose a name their ot hours appear in "K9". Now when I try to do the same in "H9" to bring back there qualification it gives me an error. I also tried to do the same in "M9" for the phone numbers and it didn't work.
View 7 RepliesA1 is 10 (10 hrs worked) , A2 is 10:30am (in time), A3 is 9:00pm (out time), A4 needs to be the total hours and minutes between A2 and A3 based on the hours listed in A-1. What i need is a formula that will calculate the hours and minutes between the 2 times based on hours entered in A1 but that will also compensate for a manadatory 30 minute lunch that needs to be deducted from the total hours if hrs listed in A1 are more than 6.
example: worked 10HRS, 10:30am to 9:00pm, Total hrs is 10hrs 30min, which should be just 10 since the lunch is a none work time and must be subtracted.
If a person worked more than 6hrs, they must take a lunch. if they worked less, than 6 then they don't have to. I need a calcuation to recognize the greater than, less than factor into the equasion also.
My very large document uses the CHOOSE function in many places to paste content based on the value of a COMBOBOX at the beginning of the document.
My problem is that I now need to paste an entire table for a certain CHOOSE function value. Documentation for the CHOOSE function allows reference to a range of cells such as A1:B12, however, I cannot get it to paste the table in the document.
conditional Macro or filter? Easy to record a Macro which filters, but choosing what (column) to filter based on values of a cell I'm not sure how to go about that. It means implementing IFs into a Macro, or..?
Data on "Staff List" sheet I have a list with all staff details including availability. I need to filter their availabiltiy and copy values over to another sheet. The availabiltiy shows Monday to Sunday and states for which of our offices they are are available. Here the columns BS to BY
Available Location Monday
Available Location Tuesday
Available Location Wednesday
[Code]....
Now I need the Macro to choose which column to filter based on the office set in cell C3 on the "Availability" sheet. And then filter that column for YES.
I am attempting to choose a cell containing a text string based on information placed in a raw data area.
My output from this formula will be a line of text picked from yet another reference cell, based on each of the four choices.
Removed non pertinent information and edited for clarity.
Basically...here's what I'm trying to do...
I have 7 numbers. The numbers are going to be grouped in threes.
(Ex. 123,124,125,126,127,134,....)
I want to assign column/range for each number...
1=A2:A100, 2=B2:B100, 3=C2:C100...
So for 123 the formula would be...
sumproduct(--(A2:A100>criteria),--(B2:B100>criteria),--(C2:C100>criteria))
It would be awesome to have the formula automatically calculated based on the numbers I choose.
As you can see on the example i have TEST hours.xlsx, I have a file that calculate the money every doctor should take based on the working hours.
Nights, holidays and holiday night have different price/hour.
The excel is working fine…but now I have to make a formula that separates automatically based on the beginning time and the end time of the doctor’s shift the day hours tha night hours, the holiday hours and the holiday nights hours.
In the excel I have fill the hours Manuscript, I need a formula to do that for me…
On the yellow cell I have try to find out the formula for the holiday hours but because the day is calculated due to a formula it is not working!!!
Simple Example: A doctor Is working from 21:00- 8:00 (next morning Sunday) he should have 1 simple hour (21:00-22:00) 9 night hours (22:00-6:00) and 2 holiday hours (6:00-8:00,)
In column A I have a date AND time entered. By the way, this is not via cell format, I have manually entered, say today's date and the current time. In column B I have a future date and time.
Basically, column A is the date and time a problem was given to me. Column B would be the date and time I resolved the problem. Now for the formula....Column C needs to spit out whether the problem was solved between 24 and 48 hours OR less than 24 hours OR greater than 48 hours.
I want to add employee hours (flight hours) based on the calendar (I want of sum of hours for the last 30 days on a running calendar.
View 7 Replies View RelatedI want to write a formula that will sum the hours in a table based on the name of a resource, a task name, and the month it was entered. So my table looks something like this:
Name
Task
Month
Hours
Person 1
Meetings
201201
2
Person 2
Misc
201202
1.5
There are additional columns, and it has over 80,000 rows...
For example, I thought that the formula to sum the hours for Person 1 if the task is meetings and the month is january would be:
SUMPRODUCT((A1:AX="Person 1")*(B1:BX="Meetings)*(C1:CX=201201),D1:DX)
I also tried SUMPRODUCT(--(A1:AX="Person 1"),--(B1:BX="Meetings),--(C1:CX=201201),D1:DX)
In a single employee Timesheet spreadsheet:
....................Mon.....Tue.....etc.
In:................6:65.....6:71
Out:............12:07....12:05
In:..............12:62....12:41
Out:............15:66....15:73
Hrs Worked:.....8.46......
How to format the cells in the daily columns and what formula to calculate 'Hrs. Worked'?
I would like to chart (pie or graph) sales based on the hour they occured.
In excel, I have column A - the time stamp, and column B, the sale. Note that each sale has its own unique time stamp.
i am trying to make an excel spreadsheet so i can keep track of my hours and pay at work... i know how to do mult and add but i wanted to know how to do the 2 together.... for example
Hours ST OT Total Hrs Gross Pay
8 2 10 ?
i would like help figuring out the gross pay if say for example my st time rated is 21.21 an hr, and the ot rate is 31.82 how can i get the 8 times 21.21 = 169.68 and the OT 2hrs times 63.64 to show up in the gross pay as a total of 233.32 - what would the formula look like?
I have an excel sheet which displays the data in the following format:
(See image attachment). This is just an example of the way the master list is formatted. The master list has over 18,000 lines. Basically, there's a project name, resource name, and number of hours worked for the project within that work week. I've been trying to figure out a way to create a formula or macro to search all these names and add up the hours of the duplicates.
I need excel to search this list of names and total up all hours for each matching name and place each total value on another sheet. For example, Ken Craver has 8 occurances within the week of 1/21/2007 (from project 1 and 25) and his total hours = 31. So the value 31 should be calculated because all the hours associated with Ken Craver add up to 31. Then excel should scan another sheet which has a list of resource names and once it has found the matching name on the other sheet, it will place the value 31 in the column next to the name.
In a single employee Timesheet spreadsheet:
....................Mon.....Tue.....etc.
In:................8:00.....8:30
Out:............12:00....12:00
In:..............13:15....13:00
Out:............17:45....17:00
In:................0:00....19:00
Out:..............0:00....20:15
Hrs Worked:.....8.5......8.75. How to format the cells in the daily columns and what formula to calculate 'Hrs. Worked'?
I just want to filter and sort the data based on names and hours.
Here it goes:
1. Filter Column F.
2. Filter it by: UK & M UK
3. After that, filter Column E in "Ascending order"
4. Count the data that are:
-starts 1:01AM - 7:59AM and display the total count in Sheet2, if there are no data in other specific time, then display a value "0"
For example for UK and M UK:
In Sheet 2, ("F5") = 3 as there are 3 data, G5=1, H5=0 and so on and so forth..
I've attached my file : Filter and Sort.xlsx
For the past month now, I'm trying to calculate some work hours (night hours actually), based on a reference.
Let me show you the table:
- Column A has all of the schedules of the employees
- Column B has the numbers of hours for each schedule (all have 8.5 hours/day)
- Column C has the reference for the night hours.
Now what I'm trying to do, is to find a formula that will calculate the numbers of the night hours using the reference in Column C
Row 23 in that table has an example of what I'm looking for.
I calculate total labor hours entered into a CRM system and use it for various reports, but it's incredibly inaccurate for the following reasons:
Any labor put in between 6pm EST and 8am EST from Mon-Fri is considered "on call"All labor from Friday 6:01pm - Monday 7:59am is considered "on call"Labor entered on a company recognized holiday is considered "on call"Not every employee takes call
So, what I'm trying to do is take the total labor and pull out only the hours entered outside of the criteria above. If it were simply based on time during the week, i'd be fine, but I have no clue how to do everything else.
In the end, my new labor hours would be split into "Business Hrs. Labor" and "On Call" labor.
If a Rescue Officer is called out at 23:00 and is back at 04:00, this should equate to 5 hours worked.
It seems that if my times are all on one side or the other of a 24 hour cycle, my calculation work fine but it it breaks across the 24 hour (as above, it doesn't work.
A2=04:00
A1=23:00
Using (A2-A1)*24 give me -19.00 hours
My SS macro has a line:
s = (wks.Cells(c, 3) - wks.Cells(c, 2)) * 24 'calculates the duration of time worked
Is there any way of getting excel to calculate an elapsed time in hours when the start and end times roll over from one day to the next?
I've being trying t calculate an excel formula to work out a order turnaround time based on opening hours.
Example: The store is open from 8 am to 6pm monday to friday, I need to get a formula to work out how long it took from the time the order was logged until it was completed, during the course of the working day - this works fine but if the order was received on 06/12/07 at 5:20 pm then was finally completed by 7/12/07 at 11:55 the next day I need to figure out how to take into consideration of non working hours during that time. (time from 6pm to 8 am next day
example 2 - if order was sent on the 06/12/07 at 01:30pm and completed at 06/12/07 4:30 then I know its taken 3 hours to complete
example 3 - if the order was sent on the 06/12/07 at 5pm and completed next day at 07/12/07 at 10am - then I know it took 3 hours to complete
It's been several years sine I had to look at calculating amount of time worked.
Can you please look at this old spreadsheet of mine and verify that the formula is correct?
It appears to be ok to me, but I don't want any errors when it comes to paying my employees
Formula: ...
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.
The below formulae allows me to see the difference between two dates and only returns the difference in working hours ie :
Difference between
02/02/2010 08:00 & 03/02/2010 08:00 is 16 Hours 0 Minutes
=(INT(A3)-INT(C6))+MAX(MOD(A3,1)-MAX(MOD(C6,1)))
The following displays it in the Hrs and Mins format
=TEXT(B15,"[h]")&" Hour"&IF(OR(TEXT(B15,"[h]")+0=0,TEXT(B15,"[h]")+0>1),"s "," ")&MINUTE(B15)&" Minute"&IF(MINUTE(B15)1,"s ",""))
I need a VBA macro code to get the output like in the excel file which I have attached with this thread. Which means, I need to group the data for every two hours. in the output I need all 4 cols namely A, B, C, D along with extra col namely, interval based on which I need these groupings to be done..
The code has to be really flexible so that it works for all dates and times in the files. Because like this I have to do for 2000 files.....
Original link: [URL] .....
plant area.xlsx
I need to show the size of a project in a cell based on it's number of hours, e.g.
Between 5-80 hours = SMALL
Between 80-200 hours = MEDIUM
Over 200 hours = LARGE
How can I do this? I can't get the IF function to work like this (unless I'm doing something wrong) and I thought about using a pivot table but think there must be an easy formula to use?
I charge one rate for day work and one for night shifts. My spreadsheet is set to figure the total number of hours worked and I know how to multiply by dollars to get answer #1, but is it possible to use a formula to multiply times a different rate for a night shift?
For example I use =IF(B2
I would like to calculate staff meal deductions for each employee based on the number of hours they worked for that day.
I have the hours listed all on one page, per day, per employee.
I have the staff meal deductions range listed on another page in the workbook.
The range is as follows: *Please note anyone working over 8.00 hours is deducted a maximum of $2.40 for that shift/day.
Hours
Deduction
1.00
$0.30
[Code].....
I'm attempting to make a simple time sheet for a handful of employees. I'd like to enter the clock in time and clock out time for each day. The end cell should be the running total for the week. The tricky part for me is having the formula subtract an hour for each day that is over 5 hours.
View 3 Replies View RelatedFormat Time Cell For Greater Than 24 Hours: Hours & Minutes Only .....
View 9 Replies View RelatedI'm having difficulty to calculate hours between 2 or 3 days exclude non working hours.
Attached is the example of start date with time & end date with time.
The situation is like "when the case log in till the case assist in working hours." so i will get the hours from case log to case assist.
Testing.xlsx