Free Microsoft Excel 2013 Quick Reference

Total hours worked per week (Formula)!!

If I had a list of hours worked, continuing down column A, and I wanted to calculate the total hours worked displayed as hh:mm what formula would I need? The list is also formatted as hh:mm.

Thanks in advance

Ryan UK


In order to calculate time worked per week in a time sheet, excel needs to
provide support for hh:mm formats where the hh argument is greater than 24,
for example if I worked 40 hours and 15 minutes in a week, I should be able
to add (8:00+7:30+8:30+8:00+8:15)=40:15. I should be able to calculate
40:00-(time worked so far) to get how much time I still need to work this
week.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

I'm trying to figure out what the formula is to multiply the total hours
worked per week by their hourly pay for each individual. kind of like
the
=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

--
charitydc
------------------------------------------------------------------------
charitydc's Profile: http://www.excelforum.com/member.php...o&userid=35682
View this thread: http://www.excelforum.com/showthread...hreadid=554658

I'm trying to figure out what the formula is to multiply the total hours worked per week by their hourly pay for each individual. kind of like the
=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

In order to calculate time worked per week in a time sheet, excel needs to
provide support for hh:mm formats where the hh argument is greater than 24,
for example if I worked 40 hours and 15 minutes in a week, I should be able
to add (8:00+7:30+8:30+8:00+8:15)=40:15. I should be able to calculate
40:00-(time worked so far) to get how much time I still need to work this
week.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

Problem:

Listed in columns A & B are the times an employee signed in and out of work each day.
Column C contains the rate per hour for that particular day.
We want to calculate the employee's total pay.

Solution 1:

To calculate the number of hours worked per day, enter the following formula in column D:
=B2-A2+(A2>B2)
Then calculate the pay per day by entering the following formula in column E:
=C2*D2*24
Finally, use the SUM function as shown in the following formula to sum the results from column E:
=SUM(E2:E7)

I know this is probably easy to do but i've been trying for hours and looked on the internet with no luck. I'm basically trying to set up a spreadsheet which will keep track of the hours i've worked and total them up for me.

I've attached a copy of the kind of thing i'm hoping to achieve and have entered an example weeks work and manually worked out the hours each day and the total hours worked that week, but all i want to do is to enter the hours i've worked and have it work out the total for that week for me. The main problem i kept coming across was when i worked, for example, 6 hours and 30 minutes, i want this to show as 6.5 hours.

I would appreciate any kind of help on this and thank you in advance for any advice you have for me.

Thanks again

Hey all,

I have a lovely budget projection spreadsheet, but for the last several months a certain formula has eluded me. I want to find out how many hours are required to work per week in order to hit a certain savings projection. That of itself is fairly simple, but the problem is more complicated than that.

I have a cell, named projected_expenses, that draws from my projected budget over a certain number of weeks left. That all works fine. The big problem is that I want to include taxes in my expense projection, but taxes depend on hours worked, so it always becomes a circular reference to try and figure out exactly how many hours I need to work to hit a certain amount of dollars when that dollar amount depends on the amount of hours.

This is the formula that returns the net amount of money including expenses over time that I need to save. Projected_expenses does not include taxes.

This cell is named Net_needed:

=Desired_total_on_date-current_savings+projected_expenses+(pocket_change_week*weeks_left)+(Hours_worked_week*wages*weeks_le ft*tax_rate)

The last set of parenthesis determines taxes over the period.

I calculate hours_needed using:

=(Net_needed/wages)/weeks_left

I know that when the hours_worked cell and the hours_needed cell are equal, that the value will be the correct amount of hours needed including taxes, but I don't want to have to sit there and plug in numbers every time to figure it out.

To summarize my intent:

I want to save X amount per week. Then I want to calculate how many hours I need to work to pay for all my expenses (including tax!) and save exactly X amount per week over a period of time.

Any ideas? VBA or simple formulas? Tables and lookups?

Thanks,

Josh

Tothineownselfbetruthful@gmail.com

I am having trouble calculating total hours worked for nightshift usually starting at 18:00 and finishing at 06:00 which the formula I have works fine until the shift starts earlier for example:
In column A the cells are formatted with TIME
A8 - Is the shift start time
A40 - Is the shift end time
A41 - Is the number of people on shift

When A8 = 17:00 and A40 = 06:00 the next day or total hours for the shift = 13hrs

I was using formula =IF(A8>A40,A8-A40,A40-A8+24)*24*A41 but it calculates to 11 where I should be getting an answer of 13.
What am I doing wrong?

I am using Excel 2007

Thanks,
Ian

Hi,

i am trying to calculate using VBA, the no of shift hours worked per
day.

each day hours =9 hrs.
shift starttime =9:00 am & shift endtime=6:00 pm.(total 9 hrs in a
day)

e.g:
===
I got some work on Thursday 5:00 PM and if we finish it on Friday 6:00
PM than it will be considered as 1 Day 1 hour job
thursday - 6:00 PM-5:00 PM=1hr
friday - 6:00 PM-9:00 AM=9 hrs
total=10 hrs or 1 Day 1 hour

Things to be considered while writing the code:
1 Day = (9 AM to 6:00 PM) - that's our shift timing.
*** Weekends should be excluded (for ex: if we got some work on Friday
5:00 PM) and we finishes the work on Monday 01:00PM) than this should
be considered within one day only [Friday 1 hour (06:00 PM - 05:00 PM)
+ Monday 4 hours (1:00 PM - 9:00 AM)

The total productive hours for each day should lie between 9:00
am-6:00 pm, excluding weekends(saturday & sunday).

I want to calculate accurately the total hours worked during workdays
between the given shift times.

i am able to calculate the no of days worked excluding weekends using
a do while & select case using weekday function like this:

Module1 Code
=====================
Function TurnaroundTime(startdate As Date, enddate As Date, stime As
Date, etime As Date)
On Error GoTo dt_err

Dim days, hr
Dim starttime, endtime

Set starttime = ThisWorkbook.Sheets("SLA Hrs").Range("Shift_start")
Set endtime = ThisWorkbook.Sheets("SLA Hrs").Range("Shift_end")
days = 0
hr = 0

Do While startdate

I want to add the total hours worked in excel that I book for the month for
example:

A B C
Start Time End Time Total
08:00 16:00 08:00
08:00 14:30 06:30
08:00 15:00 09:30
Total 00:45
It stop at 23:59 and I want it to go on like a total sum, wat formula must I
use to do my total sum calc for my time? please help

Frankie
e-mail: frankiedeconing@harmony.co.za

I am trying to work out at total hours worked per day formula, currently it adds all hours together but i need to ignore times that overlap each other i.e

Person A starts at 0730 finishes 0900 total hours 1.5
Person B starts 0800 finishes 1300 total hours total hours 4 as i was already working at 0800 with person A so it cancels out 1 hour of the actual time, so would count only from 0900 to 1300hrs, Is there a way of calculating this automatically where the overlapping times would be ignored.

Thanks for the help in advance.

I'm trying to figure out what is wrong with this formula...

=(SUMIF(Q14:Q4995, "<="&EOMONTH(TODAY(),0),W14:W4995))-(SUMIF(Q14:Q4995, "<="&EOMONTH(TODAY(),-1)+1,W14:W4995))

I've got a cell that adds Total hours worked which pulls from the same column of entered data as the formula above and that cell works. My hours for the month however just shows up as zero. If I try and edit the formula or even just highlight it to copy it and then tab out of the cell this shows up...

1/0/00

If I undo the highlight and tab it will go back to showing zero. I've checked my dates that I entered and they are correct. I'm at a loss as to how to fix this formula.

(Title was supposed to say: Counting total hours worked within multiple days)

Is there anyway to calculate the # of work hours spent on a project given 2
seperate dates and times, taking into account a work day of 8-5 with an hour lunch?

For example, if I work 8-5, start a project on 8/3 at 9 and finished it on
8/7 at 4, the answer would be 22 work hours. Ideally, i'm trying to avoid having a lunch out/lunch in column or having to enter each day into a spreadsheet...

I'm thinking a datediff equation

Any ideas would be greatly greatly greatly appreciated.

Thanks!

I keep track of my daily work hours using Excel. I enter starting time in one column and ending time in the next. I would like for the next column to calculate the total hours worked and rounded to the next 1/10th of an hour. I know it can be done, but I haven’t figured it out yet. Any help would be very much appreciated.
Popeye

I am attempting to create a time sheet that will automatically display total hours worked in a day/week/pay period, regular hours worked in a day/week/pay period, overtime hours worked in a day/week/pay period, and double time(holiday) hours worked in a day/week/pay period. The pay period is 2 weeks in length and the hours are automatically calculated by entering in 1-clock in times, 2-clock out for lunch, 3-clock in from lunch, 4-clock out for the day. Cell H12 displays the total hours worked for the day (allready have that), now I need column I to display the total regular hours worked for that day. A regular work week is 44 hours in length, anything more is considered overtime. I need the values in column 13 to display a value between 0 and the total hours for the day without displaying anything that was worked on that day past the 44 hour mark. I have dedicated a cell (M3) to hold a static value of 44 hours (displayed as 44:00)... I came up with this formula which I'm pretty sure is close, but does not do what I need it to do:

=IF($H$12:H12

I am trying to make a spreadsheet of the daily hours worked and a total of
the weekly hours but i am not having any luck, at the moment I have the
following

H3 08:33, H4 08:01, H5 07:23, H6 08:15, H7 08:15, when i type =sum(h3:h7) i
get 16:27 instead of 40:27 i would appreciate any advice

thanks

Hi,

I would like some help with a formula.

I have a spreadsheet which has columns with date ranges and an amount of hours worked during that date range. I then have other columns with different date ranges. These date ranges cross over with the date ranges of the first ones. I need to find out how much is earned in the second lot of date ranges from the other information.

It's hard to explain, but if you take a look at the attachment you will understand. Under the first lot of columnns you will see that there are week and fortnightly date ranges. Then total amount of hours worked in that period. I then have a column which works out the amount of working days during that period. I then have hours worked per day, hourly rate and a gross amount for that period. I then have a different set of date ranges. I need the formula to calculate the hours worked and gross amount earned in the second lot of date ranges based on the info from the first set of date ranges. For example: fortnight 22/1/12 - 4/2/12 the person worked 53 hours at $25 per hour.This equals a gross amount earned of $1325. And for the fortnight 5/2/12 - 18/2/12 the person worked 76 hours at $25 per hour. This equals a gross amount earned of $1900. Now, the other fortnight date range shows fortnight 24/1/12 - 6/2/12. Based on the info from the first columns I need to work out what the hours and gross earnings are for the period 24/1/12 - 6/2/12.

Thankyou for all your help

I am a lorry driver by trade and wish to create a Timesheet in Exel 2002. My
problem is that I although I can calculate the hours that I have worked on a
daily basis, I cannot find or work out a formula that will calaculate my
daily rest period, (Rest period is calaculated from time finished and time
started next day) and the total hours that I worked for the week. Can anyone
help me please?

I have set up a worksheet where you enter start time and finish time and
total hours worked for the week but then I want to multiply the result by
rate per hour but when I do I get the result in time format not currency. Any
Suggestions.

I am working on a spreadsheet which contains how many hours worked per
day and totals for the week and month. I am using the
=TEXT(B2-A2,"h:mm") formula to get the difference betwen the time I log
in and the time I log out to obtain my total hours for the day. Now how
do I add all those times to get the total worked for the week?
Then I want to total those 4 values for total time worked during the
month.
Should I not use the TEXT function? I tried just subtracting them but
then the total time does not come out right.
THanks for you help.
Jennifer

--
jlogan21
------------------------------------------------------------------------
jlogan21's Profile: http://www.excelforum.com/member.php...o&userid=30111
View this thread: http://www.excelforum.com/showthread...hreadid=497970

I have done a time sheet in excell. the columns are Employee name, Time in,
Time out, Total Hours,- 30 Minute lunch,total hours worked for the day. I am
now trying to total the number of hours worked for this employee for the
week. I did auto sum but the number will not go past 24. How can i get this
total for the week. I have the cell formatted as follows
Format>cells>Number>Custom{h}:mm . Here is what my colmun looks like. i
should have a total of 48:50 what am i doing wrong

10:30
0:00
10:15
0:00
0:00
10:00
9:00
8:00
23:45

I have done a time sheet in excell. the columns are Employee name, Time in,
Time out, Total Hours,- 30 Minute lunch,total hours worked for the day. I am
now trying to total the number of hours worked for this employee for the
week. I did auto sum but the number will not go past 24. How can i get this
total for the week. I have the cell formatted as follows
Format>cells>Number>Custom{h}:mm . Here is what my colmun looks like. i
should have a total of 48:50 what am i doing wrong

10:30
0:00
10:15
0:00
0:00
10:00
9:00
8:00
23:45

I need to calculate the total number of overtime hours worked in a 4 week period using employee numbers to identify the worker. Each employee may have multiply entries.

I have absolutely no idea how to get the calcuation to look through the spreadsheet looking for duplicate employee numbers and totalling their hours. (Full spreadsheet can have over 1000 entries)

Any help would be much appreciated. I have attached a sample spreadsheet.

Thanks

I am working on a spreadsheet which contains how many hours worked per day and totals for the week and month. I am using the =TEXT(B2-A2,"h:mm") formula to get the difference betwen the time I log in and the time I log out to obtain my total hours for the day. Now how do I add all those times to get the total worked for the week?
Then I want to total those 4 values for total time worked during the month.
Should I not use the TEXT function? I tried just subtracting them but then the total time does not come out right.
THanks for you help.
Jennifer