Man Day Calculation And Auto Sort 
Man Day Calculation And Auto Sort  Excel 

I'm working with Excel 2003 sp 3 on Windows xp.
I have been banging my head trying to jar loose the formula to calculate man days where 1 equals 8 hours. I have one that was given to me by a coworker:
=INT(Q3/8)+(((Q3/8)(INT(Q3/8)))*0.8)
That calculates man days from total hours. I need a formula that will total man days and return the man day answer. Example:
weekending 12/25 = 16.2 which is 16 man days and 2 hours
weekending 1/1 = 24.7 which is 24 man days and 7 hours
Total = 41 man days and 1 hour
I have also been asked if there was a way for a number to be entered into a spreadsheet and automatically put in order without a macro or "pushing a button".
Any advice is greatly appreciated!
Happy New Year!
~ Brenda ~
Similar Topics
I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.
The days of the week consists of "Name" "Date" Hours"
The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"
Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.
I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.
I have attached a sample file.
Thanks in advance for your help.
I made a simple worksheet that we have been manually entering the time for employees, but there are too many errors (even with simple math). Can someone help me convert the time of ex: written 84:30 or 103 (meaning 8:00am to 4:30pm) where you have the total weekly hours? Right now I have a column for overtime. Is there a way to automatically calculate the over time also? The work day is 84:30 with a half hour lunch (lunch is not calculated into the hours, so you minus a half hour). 84:30 equals 8 hours. So, if a person works 8am to 6:30pm, how can I set it up where in the first row of time, I will type in 86:30pm. In the totals column to the far right, it will display 8 hours. In the (O.T.) column, it will display 2 hours.
This way, when I call in my payroll, I will have the total "regular" hours in one row and underneath, I will have the total overtime hours.
Or... does anyone have a better solution to keeping track of their hours?
Thanks!
Marty
There is about 20 staffs in our department and I have made a spreadsheet with 12 tabs for each month of the year JanDec. If each staff have 15 days annual leave each year and how can I make a formular that can automatically calculate the balance of annual leave and entered in the tabs of the following months. For example if staff A used 5 days in Jan how can the balance of 10days be entered in the tabs from FebDec. If he use another 5 days in July the remain balance of 5 days be automatically entered in the tab from Aug to Dec. Likewise if staff A took 2 days sick leave in Jan, and 2 days in Mar how can this sick days be automatically sum up in the months after. I am doing this manually at the moment and it is very time consuming and also very easy to make mistake.
Thanks and sorry for this long questions
I have a dillemma here,
I have to create a schedule which incorporates lunches automatically (either half hour or 1hour lunches, depending on the circumstances).
The schedule only has time in and time out, but I need excel to automatically deduct the lunch break depending on the circumstance:
If you worked less than 6 hours = no lunch
If you worked more than 6 hours, but less than or equal to 8 hours = 0.5 hour lunch
If you worked more than 8 hours = 1 hour lunch
I am assuming the formula for this will be really long, but I have looked online everywhere and have not found ONE formula for it. I can't put lunch breaks seperately, so all I have to work with is Time in/out.
Also, I wouldn't be able to use military time, so I don't know how excel can assume that time in is in the morning, and time out is in teh evening and/or halfday.
If someone can help it would be greatlyyyy appreciated
3 & 4 shifts = 15% Basic
2 shifts = 10% Basic
If on leave for 21 working days or more, no shift allowance
If on leave for less than 21 working days and on 3 or 4 shifts, shift allowance prorated as (Basic*15%)/21*(diff. btw 21 and no of days leave observed)
If on leave for less than 21 working days and on 2 shifts, shift allowance prorated as (Basic*10%)/21*(diff. btw 21 and no of days leave observed)
The formular I used in the attached worked but the only problem is that I had to sort by no. of shifts and adjust the formular first. I need A formular that does not require sorting. I tried to combine IF, AND, OR formulae but I got error. Please assist me sort this out. Thanks a bunch.
Please help!!!
We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:
From the hour mark to 6 minutes = 0 time paid
Over 7 minutes to 15 minutes = 1/4 hour 15
16 minutes to 30 minutes = 1/2 hour 30
30 minutes to 45 minutes = 3/4 hour 45
46 minutes to 60 minutes = one hour.
They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):
WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
5/16 5/17 5/18 5/19 5/20 5/21 5/22
TIME IN ENTER TIME, INCLUDE AM OR PM
TIME OUT
TIME IN
TIME OUT
TIME IN
TIME OUT
TIME IN Total Hours First Week
TIME OUT
HOURS WORKEDOR choose one
*HOLIDAY WORKED
PAID HOLIDAY (not worked)
*OTHER PAID HOURS
EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER
I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.
Thanks..
What can I do to countdown the days as below AND countdown the days that have passed as a NEGATIVE NUMBER???
=DATEDIF(NOW(),D11,"D")
I have sheet muster for my clients of January, 2005 like :
( P = Present, A=Absent )
A.....B.....C.....D.....
Days Sun Mon Tue Wed
Date 1 2 3 4
1 John P A A P
2 Lucy A P P A
3
Now I want to calculate the total Sundays, Mondays, Tuesdays etc. present in
that month. How can I do this?

Knowldege is Power
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell  then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).
for example: if an employee works 10 hours a day we would not count towards overtime until the employee completes a 40 hour work week.
Any suggestions would be greatly appreciated!!!!!
Thanks,
YV
=Sum(I25:I32) Formula but I want to multiply the outcome of that formula by the hourly rate of 11.00, 15.00 and 16.00. Does anybody know how this can be done? If so I would greatly appreciate it.
Thanks!
charitydc
I have two columns of data: column A contains the date and time in the form dd/mm/yyyy. Column B contains a number value. All the times are in order, so column A looks like:
01/01/2007
02/01/2007
03/01/2007
I have data from 2007 to 2010. I need to calculate an average daily value (in column B) for each month, and display it in column C. So, for January, I need to calculate the average of 31 days, February, 28 days, etc.
Is there a function I can use to do this? I've been trying the AVERAGEIF formula but can't get it to work. Any ideas would be much appreciated!
Many thanks,
Caitlin
Please can someone help me. How do I create an IF formula to find cells where the number of hours in one cell is more than the number of hours in another cell? eg one cell has 0.40 hrs and another has 02.25 hours  I want have an IF formula to say where 02.25hrs > 0.40 then type "FAILED" but I cant overcome the greater than problem on the hours which is giving me the wrong answer.
example;
8:00 to 8:07 = 8.0 hrs
8:08 to 8:23 = 8.25 hrs
8:24 to 8:38 = 8.5 hrs
8:39 to 8:53 = 8.75 hrs
8:53 to 8:59 = 9.0 hrs
I am having trouble writing an equation that would sum the clockin and clockout times (2 per day) and display the total time in these quarter hours and adding an additional hour if it is >= to *:53. Any help would be great.
Cell B5: start time: 4:15 formatted as 4:15:00 am
Cell B6: finish time: 6:15 formatted as 6:15:00 am
Cell B7: total time: 2:00 formula in cell: =TEXT(B6B5,"h:mm")
Cell B8: total batches processed: 22 (this is entered manually)
Cell B9: batches per hour: formula in cell: =B8/TEXT(B7,"h")
as long as I have this formula in cell B9 the answer comes out correct which should be 11 per hour.
If the formula in B9 is B8/B7, the answer is 264.0, Is this because of the way excel is reading the total time or the total time is formatted as a time, not an actual number? Is this the correct way to solve the problem?
answer should be 11 per hour.
Basically, I've got a sheet that contains a table of entries, and each entry gets a date stamp in the first column. I would like to have the sheet display the average time, in days, between entries. Is this possible?
Thank you in advance!
~cp
inbetween the two dates. Does anyone know how to then convert the number of
days into months?