Formula To Add Up Hours That Were Attended For Various Individuals

Jan 5, 2009

I'm trying to figure out how to write a formula to add up hours that were attended for various individuals. My data is coming in from scanning their barcodes, so I'd rather not manually add their hours. I have these columns: Date, Time, First, Last, Member ID, CEU. My hope is that I can take the Member ID and write a formula that adds up the CEU column.

This issue I'm having is how to deal with the variables of someone only attending two sessions, while another attends four? I'm not sure how to do this so I'm seeing if anyone knows if this can this be done?

View 9 Replies


ADVERTISEMENT

Formula That Will Work Out Score Of Individuals

May 31, 2012

I currently have a sheet of data with names and scores (1-10) on e.g.

NameScoreJohn10Smith5Steve4John9

and what i am wanting is a formula that will read these columns and tell me the amount of 1's, 2's, 3's, 4's, 5's, 6's, 7's, 8's, 9's and 10's the corosponding name has gotten.

for example:

JohnSmithSteve123415167891101

I have been playing around with COUNTIF formulas but cannot seem to work this out.

View 5 Replies View Related

IF Formula- Comparing Height Vs Weight Standards Of Individuals

Jan 12, 2010

i am trying to use the following formula in a cell. it is comparing height vs weight standards of individuals....

View 9 Replies View Related

Getting Percentage Of Attended Meetings

Mar 31, 2014

I am working on a spreadsheet to keep attendance at meetings. I have used "X" to mark someone present. I have calculated all of the "X's" to once column then I want to get a percentage of meetings they attend. Here is what I have:

=COUNTIF(A3:V3,"X") - This gives me the number of meetings attended there are a total of 21 scheduled.
=(X3/21)*100 - This should give me the percentage of meetings attended.

Problem is I get 476%, I know it will be something simple, just wanted to ensure the formulas were right and it should be something simple.

View 8 Replies View Related

Looking For A Formula That Can Separate Day Shift Hours From Night Hours?

Aug 6, 2013

I have got a formula that can separate day shift hours from night hours, in this case night begins at 7pm to 7am, however the problem is after 12am we get into negative numbers, what formula would fix this and can be combined with the formulas below?

E10 = 19:00 or start of night time hours
B3 = start time
C3 = end time
D3 = day hours workeds =24*IF(E10

View 1 Replies View Related

Convert Hours Worked In Week To Hours Worked In Month With Formula?

Feb 21, 2013

Is there a way to conver a persons time spent (given in weeks) to adjust/convert to show per month. Attached is the sheet. Do note that week 2/25 - 3/1 is a combination of Jan and Feb so hours should be logically divided into jan and feb...

Name 2/18 - 2/22 2/25 - 3/1 3/4 - 3/8 3/11 - 3/15 Feb mar
Tom 40 10 0 20 ?? ??
name
2/18-2/22
2/25 - 3/1
3/4 - 3/8
3/11-3/15
Feb
Mar

tom
40
10
0
20
??
??

View 3 Replies View Related

Separate Simple Hours / Holiday Hours And Night Hours

Jun 27, 2014

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,)

View 1 Replies View Related

Schedule Report - To Be Generated For Each Individuals

Aug 7, 2008

I have a schedule sheet (on week days what assignment is be to taken in concerned period).

I need a report based on the same. i.e. I need to generate a report for each individual.

I have attached a sample file, in which I explained the requirements.

View 12 Replies View Related

Sheet That Records Performance On Individuals

May 1, 2009

I've a sheet that records performance on individuals, what it does is collate the performance daily on certain things.

Most are simple plus/minus one and i have control buttons that do this,what it does is: On click it adds one to a different sheet, or if to remove it minuses one, and there is a total box to sum these figures.

But there is also some provision to add collected money value, and what i would like to do is have a function that deletes the last figure added to the financial transactions
This is the code
If Target.Address = "$A$6" Then Sheets(2).Range("A65536").End(xlUp).Offset(1).Value = Target.Value

So for example
in A6 £10.50 is added, but i can't think how i can provide a service that will delete the last figure in Sheet 2 range A, as this will be constantly changing.

View 9 Replies View Related

Total Monthly Values For Individuals

Aug 31, 2006

I have monthly a list of 2,500 people with an allowance value for each individual shown.

Problem I got is that sometimes there is more than one allowance entry for the individual in each month. I just need the total for the individual in that month. Each month is on a separate worksheet. I have been racking my head, but I can’t figure how I can look at the list of people (who have a unique number) and just total it.

View 9 Replies View Related

Calculating Individuals Exact Age Given Their Date Of Birth

Jan 21, 2004

I am looking for a formula that calculates an individuals exact age given their date of birth. That shows just the age in years.

View 9 Replies View Related

Pivot Tables - Finding Number Of Individuals With Particular Filter Combination?

Jan 30, 2013

I am using a pivot table to explore the relationship between the two variables in a survey. Question 1 is a "choose all that apply" question, while Question 2 is more "if you said yes to any in question 1, choose all that apply".

So, I have about 10 filters for question 1, where each combination will give me a different number for the responses for question 2. My question is this: How do I get excel to automatically find the # of individuals with the particular combination of filters in question 1?

Ie. if said yes to 3/10 options in question 1, how do I get excel to find out how many actually said yes to the 3/10 questions.

The purpose of this data is for reference to question #2, so that i can utilize some percentages.

View 1 Replies View Related

Solve Between 24 And 48 Hours OR Less Than 24 Hours OR Greater Than 48 Hours

Nov 4, 2008

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.

View 9 Replies View Related

Formula For Kilowatt Hours

Jul 1, 2008

I am creating a comparison sheet for kilowatt hours used, in D29 I have the actual KWH used. What I need is a formula that will give me the cost of the 1st 400 KWH at $0.08570 per KWH in BV7, the 2nd 400 KWH at $0.12175 per KWH in BW7, and the cost of the remaining KWH at $0.14427 per KWH in BX7. Now be advised that some times the KWH may totol 653 for the month and sometimes 1950 for the month.

View 9 Replies View Related

Formula To Calculate Hours

Jan 3, 2007

im doing an excel spreadsheet. on 1st sheet i have a summary page for some clients, on sheet too i have worked out and done the formula so that it calculats values i put it. i want to take this total value i have worked out (which is a variable- will change from day to day) and move this value to sheet one. from here i want the number that i show in the summary page to deduct from a fixed total in the cell nxt to it. and ideas ive tried several options coming close but no ciga

View 2 Replies View Related

Sum Formula - Calculating Overtime Hours

Feb 20, 2014

Example: In cells A1:A10 random number between 0 & say 20, need to sum ABOVE 8 = (calculating overtime hours)

E.g.
A1 = 0
A2 = 8
A3 = 8
A4 = 10 (giving 2)
A5 = 12 (giving 4)
A6 = 5
A7 = 13.5 (giving 5.5)
A8 = 8
A9 = 0
A10 = 16 (giving 8)
A11 = (Total overtime above 8 hours) 2+4+5.5+8 = 19.5

Need to be able to increase rows and drag across.

View 3 Replies View Related

Formula To Get HOURS From 2 Dates With Time

Jun 30, 2014

How to get the Hours of two different time

A1
27/06/2014 11:30

B1
30/06/2014 18:14

C1

Ans: Hours of these 2 dates with time.

i just need to get their Hours.

View 3 Replies View Related

Formula Hours And Minutes To 1 / 4 Hour?

Mar 1, 2012

I have a program that outputs a total amount of time that a service was provided. This time is reported in hours minutes [Example: 01.08 (hh.mm)]. I need a formula that will "round" to the closest .25 based on a 7 minute window on either side. Here how it would need to work....

0 - 7 Minutes = .00 or 1.00 - 1.07 = 1.00
8 - 22 Minutes = .25 or 1.08 - 1.22 = 1.25
23 - 37 Minutes = .50 or 1.23 - 1.37 = 1.50
28 - 52 Minutes = .75 or 1.28 - 1.52 = 1.75
53 - 7 Minutes = .00 or 1.53 - 2.07 = 2.00

View 4 Replies View Related

Formula For Calculating When Data Is Out Of Hours

Mar 29, 2012

I am trying to calculate when data is out of hours based on it being before 08:00 and after 16:30, plus also weekends.

I have used the following formula to calculate the day of the week.

=TEXT(WEEKDAY(AE2), "dddd")

I have this in a separate column.

This is how my sheet looks.

Actual Start TimeOut of HoursActual Start DateDay of Week
14:1525/12/2010Saturday11:0028/12/2010Tuesday22:3028/12/2010Tuesday
13:3029/12/2010Wednesday09:4030/12/2010Thursday13:0002/01/2011Sunday
09:3806/01/2011Thursday08:0507/01/2011Friday12:0009/01/2011Sunday
11:1010/01/2011Monday08:1512/01/2011Wednesday10:1113/01/2011Thursday
11:1514/01/2011Friday15:2815/01/2011Saturday10:4016/01/2011Sunday
16:3028/12/2010Tuesday13:0220/01/2011Thursday13:0624/01/2011Monday
15:0827/01/2011Thursday17:4029/01/2011Saturday

The out of hours column is where I figure I need an IF statement but not sure which way to approach it best.

View 2 Replies View Related

If AND Formula Using Hours And Minutes - Query

Jan 23, 2009

I am trying to write a formula that will take the value of cell J9 which is in hours and minutes format (hh:mm) and return a value in £.
ie. if J9 5hours and 12hours £15.00

I used this
=IF((J9)5,(J9)24,(J9)>24),15,))) returns #Value!
It won't work as it won't accept the hours and minutes format in J9. If I maually enter a value in J9 like 7.00 it returns the correct £12.00

View 9 Replies View Related

Formula To Calculate Hours Worked

Feb 24, 2009

I have this spreadsheet and in it the time is changed from military time to regular and then I use a formula to calculate hours worked. On some of these the total is off by one minute. Does anyone know how to fix this?

I don't know how to paste the spreadsheet so you can see formulas,

View 9 Replies View Related

Calculate Hours Worked With Lunch / Overtime / Holiday / Vacation And Saturday Hours?

Jan 13, 2014

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.

View 2 Replies View Related

Difference Between Dates And Times In Days , Hours Mins ( Working Hours )

Mar 2, 2010

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 ",""))

View 9 Replies View Related

Adding Hours Worked Minus Lunch Break IF Over 5 Hours?

Dec 15, 2013

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 Related

Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only

Jan 29, 2010

Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only .....

View 9 Replies View Related

Formula To Add Total Hours Employee Worked For Each Day?

Dec 21, 2013

Refer to the attached sheet which is Daily Schedule for employee. I need a formula to add hours worked on single day in cell C2 for Monday, E2 for Tuesday, G2 for Wednesday, and so on.

Every day we have Clockin_Clockout info for each employee as shown for employee a & b.

FYI : I am using below formula to add employee hours for the week as (formula in cell R4).

[Code] .....

View 14 Replies View Related

Productivity Formula: Calculate Worked Hours

Nov 6, 2009

i m trying to work out the productivity of employees based on how many hours they work (Time in Back Office). How many pieces of work they complete(Back Office items Completed) if 1 piece of work should take 7 mins. the item in red is what i cant seem to figure out.

View 5 Replies View Related

Formula To Total Hours And Minutes In A Column

Jun 23, 2008

I am using a formula such as =Text(A5-E5,"H:MM) to get the difference in clock-in time and clock-out time on a daily basis (Monday-Saturday). I want to add the results as a total for the week. I am not sure what formula to use to get that result. I prefer not to use decimals unless I have to. Also, the above formula does not work when the time goes past 12 midnight.

View 9 Replies View Related

Formula To Deal With Time, Hours & Minutes

Feb 1, 2010

I've got a worksheet that deals with dates and time. Similar to a checkbook, I need to take the balance and subtract what's taken from that. But the writer copied this from another software program and it's in this format. How do I convert it to a time format? Or is that what I really need? Right now the taken and balance are both in a general format.

I just need to know on any of the given days what the balance of this person's time was in hours and minutes.

View 2 Replies View Related

Formula That Will Sum Hours In A Table Based On Name Of Resource

Feb 7, 2013

I 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)

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved