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

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

- Calculating a colmun to total a 40 hour work week
- Creating a formula to determine regular hours worked in a day given a 44 regular hour work week
- Calculating Overtime (after 40 hours worked)
- How to Calculate a Projected Finish Date based on Work Week and Holidays
- Is there a way to totalize data from multiple worksheets?
- Format a cell to display decimal hours.
- Format a cell to display decimal hours.
- Total the hours scheduled
- Getting a total of hours & mins worked per week
- Calculating total overtime hours worked per employee
- Calculate work hours between two dates of a work week - Sunday to Thursday.
- Calculating Time and Half and Double Time (After 40 hours worked)
- Total of hours worked
- Calculating basic hours worked, between a time range and premium hours worked
- Format Cell Totals as Hours
- =SUM(A1:A5)*24 for totalling hours and minutes is not working
- formula help,need to deduct 30 min if worked over 6 hours
- subtracting hours from time
- How do I format/convert Time -> Hours worked? (AM/PM to Decimal)
- Calculating overtime....
- Calculating daily and weekly hours worked; updating most recent excel row based on date
- Help with Payroll Formula
- Calculate working days but change working week
- How can I make a timesheet to figure my hours and payrate?

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

=IF($H$12:H12

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

I have found the xNetWorkDays code on Google Groups from Myrna Larson

and it works great to calculate the number of work days between two

dates inluding a list of holidays and whether the work week is 5 , 6,

or 7 days. What I'm trying to do is find the projected finish date for

a given task based on the start date, the duration allocated for the

task, workweek type (Sunday off, Sat/Sun off, or no days off) and a

holiday list. I've been using other formulas but can't seem to get it

quite right. Any help would be greatly appreciated.

What I would be looking for is a function with the following arguments:

= EndDate(StartDate, DaysOff, Duration,Holidays) where:

StartDate = Start of the task

DaysOff is the work week type (5 for Sat/Sun off, 6 for only Sun off,

and 7 for no days off)

Duration = Number of days allocated for the task

Holidays = a range array of holidays that are not worked

Mojado44

sheet which will give me quarterly and semiannual totals.

Instead of displaying 3:45 (for 3 hours, 45 minutes), which is one of

the standard formats, I'd like to display 3.75 (hours). h.hh doesn't

do it.

The above is formatting for an elapsed time calculation (finish time

minus start time). (On a "time card")

Excel 2000. Will a later Excel do it for me?

Some good source for a parsing function?

Any good reference?

Thanks,

Fred Holmes

Instead of displaying 3:45 (for 3 hours, 45 minutes), which is one of

the standard formats, I'd like to display 3.75 (hours). h.hh doesn't

do it.

The above is formatting for an elapsed time calculation (finish time

minus start time). (On a "time card")

Excel 2000. Will a later Excel do it for me?

Some good source for a parsing function?

Any good reference?

Thanks,

Fred Holmes

For example: Monday "clock in" 10am(cell-C10), and Monday "clock out" at 3pm (cell-D10), and so on through Sunday. Would like to know how to compute the formula to total the hours worked for the week. If anyone could lend some advice it would be appreciated. Thank you.

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

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

Please help me with a formula for the situation below.

Work week is from Sunday to Thursday (8.30 am to 5.30 pm)

Example:

StartDate: 01/06/2010 10:00 AM in A1

EndDate: 09/06/2010 04:00 PM in B1

StartTime: 08:30 am in A4

EndTime: 05:30 pm in A5

I need to calculate working hours (in decimal format e.g 30.75 hours) between the above start and end date/time considering a work day is of 9 hours and non-working days are Fridays & Saturdays along with any HolidayList.

I'm Feeling Lucky

I have attached a copy of my worksheet. Any suggestions would be greatly appreciated.

thanks

DB

How can I get a calculation of the total hours worked during the week? I also need another column showing +/- hours for the week. (I calculate time daily so if you work 8hr, 10min you have +10mins...we have comp time for anything worked over an 8hr day.)

Did my request make sense?? Can you give me some guidence??

I am trying to calculate the hours worked for a Service Engineers time sheet, my problem is we travel and work on weekends and through the night, so our basic pay is between 07:00 and 18:00, the rest is overtime, unless it is travelling time, see below for the companies explanation on this.

Site basic hours 07.00-18.00 Monday to Saturday

Premium rates 18.00-07.00 Monday to Saturday

Sundays and Bank holidays at basic rate x 180%

Travel hours Monday to Sunday basic rate x 80%

I have attached the spreadsheet so it will make more sense to you, I have filled in the Premium hours and Site Basic hours manually, these are the cells i want to automate.

I will also need to add a tick box for each day to say if it is a bank holiday or not and then include the x180%

Any thoughts on the matter will be gratefully received, if it can even be done in a formula?

Roy

I'm new here and hoping that someone can help me figure out what the problem is with my spread sheet.

I'm adding hours from several worksheets, across the entire month.

Each sheet is divided into weeks, with a column that totals the hours for that week. Those totals are summed up in another column that adds the weekly totals to get a monthly total. THAT total is then redirected to a master worksheet. On the master worksheet, I want to add the hours culminated to date with another column of hours.

All hours have been formated as time: 13:30, custom format set to [h]:mm - in the weekly spreadsheets, these calculate just fine and I get a weekly total that makes sense. However, on the master work sheet where I add the monthly totals to extra hours in another cell, no matter how I format the results, they don't make sense. Below is a copy of what I'm getting;

Child2 ......46:25 ......6.5 ........202:25

Child3 ..... 80:00 .....38.25 ..... 998:00

Child4 ..... 70:05 .....30.75 ......808:05

Child5 .....58:10 .....35 ..... .....898:10

As I said, everything is formatted to show the result I want, but the last figure in the column is just not coming out right. any help is appreciated in a big way!

noted formula if the total is going to be >24 hours. However, I cannot get

an accurate total. Any thoughts comments, would be greatly appreciated.

p.s. i've attached my time sheet.

On the daily sheet, I have a column for the employee name, a column for what time they clocked in (D8), a column that automatically deducts 30 minutes for a break (G8; using this formula: =IF(D8=0,0,1/48)), a column for what time they clocked out for the day (H8), and finally a column which shows how many hours were worked that day (K8). Essentially as the end of the week I enter the appropriate in and out data into the worksheet. Then cell K8 calculates how many hours they worked that day using this formula: =TEXT(H8-D8-G8, "h:mm"). I have set it up so that if no time is entered for that employee on a given day, then K8 reports zero hours worked that day in order to avoid a negative time value. This all works well.

So then on the eighth sheet I have a column for total raw hours worked, regular work hours, and overtime hours. Total raw hours (G8) is the sum of all the other sheet's K8 cells with the formula as such: =TEXT(Friday!K8+Saturday!K8+Sunday!K8+Monday!K8+Tuesday!K8+Wednesday!K8+Thursday!K8, "[h]:mm"). G8 thus expresses how many total hours the employee worked.

Now comes the weird part.

In K8 for the eighth/"Payroll" sheet, I wanted to create a formula that would take the total amount of hours worked from G8 and subtract 40 hours to give me how much overtime that particular employee worked that week. The problem is, I can't seem to figure out the correct formula to have it express what I need. I tried the following, but I keep coming up with a negative value (-38 with some decimals):

=IF(G8>40,G8-40,"")

Finally in I8 on the Payroll sheet, I need to be able to express regular hours worked (that is 40 hours or less). I figured I would just put in =K8-G8, but since I can't get K8 (overtime) to work, I'm rather stuck.

Please assist as my department has been calculating hours by hand, which has opened us to a number of mistakes and a great deal of embarrassment. Now that I have been made Director, I need to make this nonsense go away.

Cheers!

I have a time sheet setup in Excel, and I've tried to find my answer by searching online - no luck. I found a few things, but nothing quite the way I had hoped.

I would like Excel to simply calculate hours worked (in NUMBER format), from an AM/PM formatted cell. I've seen formulas online/formatting online that addresses this, but it doesn't end up the way I'd like it to.

http://i53.tinypic.com/1zxb30k.jpg

I'd like the hours worked to be in the NUMBER format, like hh:mm, hh.mm, etc.

But every time it always comes out wrong. I'd like the hours worked to be like: 9, 10, 8, 4.5, 9.25, etc.

How do I have to format the hours worked cell in order for Excel to convert/calculate this formula correctly?

Thank you so much everyone!

http://www.computing.net/answers/off...excel/416.html

http://www.pcuser.com.au/pcuser/hs2....256AE6002C9111

I can not realy explain what I have thus far accomplished with out showing you what I have come up with thus far so my time sheet is attached.

the format is necessary and my totals always come out right.

what I need are a day to day account for my times. any overtime must be shown in the overtime column and not shown in the standard time column

What I have done to accomplish this so far is create a =MIN and =MAX formula where it calculates that any time exceeding a set amount of hours is calculated and shown else 0 is shown in the column.

an example

the standard time is calculated by this formula

=MIN($M$30;O15)

cell O15 has a formula calculating the actual time worked

=(L15-K15+(L15<K15))*24

and the overtime is calculated by this formula

=MAX(0;O15-$M$30)

cell M30 has a fixed hour in my case "6" I actually work a 10 hour day but have the day separated into two separate columns as I take lunch at the same time of say and if overtime is worked it will be the later part of the day and will be over the 6 hour mark for the second column.

I'm sure none of this made sense so please download my spreadsheet to take a look.

when time is imputed into my spreadsheet it calculates what is standard time and what is overtime and sorts them into the correct columns

This works great however does not also account for a 40 hour work week

so I need help either adding this variable into my current equations or a new one to account for both variables.

I'm trying to track my hours worked in graduate school on various activities (e.g., writing manuscripts, programming experiments, etc.). I've setup my excel file to have a shorthand for date, so that each row marks the beginning of a new day (e.g., J1 = June 1st). On each row are empty cells that represent the total number of hours worked that day (e.g., 1.25 = 1 hour 15 mins). At the top, each column is marked by activity (e.g., writing, programming, etc.). On the side, I have a grid setup that enables me to type my start and stop time, with a separate start/stop time for each activity.

I want to enter my START and STOP times throughout the day after working on various tasks. I then want that information to be automatically added to the appropriate cell in the row that represents the current date. For example, it might look something like this:

ACTIVITY: Writing

START: 10:00 am

STOP: 11:00 am

TOTAL: 1.00

That information would then need to be added to the right cell based on: 1) the present date, 2) which activity this represents. Thus, it would find the correct row based upon date, and then the column information would never change (i.e., writing would always be in column X, programming in column Y, etc.).

The point of doing this is so that I can quickly enter my START and STOP information throughout the day, and my overall total for each day will be recorded quickly (so that if I take breaks, I can come back and re-update my START and STOP times to keep adding to my daily values). In this manner, I can get a sense of how much I typically work on any given task. Additionally, I have created a grid that I would like to use to keep running daily and weekly averages of my overall amount of hours worked broken down by activity.

If need be, I can try to clarify what I need if it is at all unclear. I specifically do not know how to write the appropriate code to dictate which row the information should be placed in, nor do I know how to subtract times and obtain a "text" value for hours (such as 1.25).

I have attached the Excel sheet I have created to give everyone an idea of what I'm trying to accomplish.

Thanks,

PoLyGLoT

I am having a difficult time creating a spreadsheet for payroll that is totally automated once I enter the hours worked for each employee. Here is what the spreadsheet looks like:

Date Day of Week

Total Regular Overtime Double Time Vacation Holiday 2/24/2008 Sunday

0.00 0.00 0.00

2/25/2008 Monday

10.32 8.00 2.32

2/26/2008 Tuesday

9.87 8.00 1.87

2/27/2008 Wednesday

10.37 8.00 2.37

2/28/2008 Thursday

9.83 8.00 1.83

2/29/2008 Friday

0.00 0.00 0.00

8

3/1/2008 Saturday

0.00 0.00 0.00

3/2/2008 Sunday

0.00 0.00 0.00

3/3/2008 Monday

10.72 8.00 2.72

3/4/2008 Tuesday

10.13 8.00 2.13

3/5/2008 Wednesday

11.18 8.00 3.18

3/6/2008 Thursday

10.38 8.00 2.38

3/7/2008 Friday

9.45 8.00 1.45

3/8/2008 Saturday

0.00 0.00 0.00

Hours

92.25 72.00 20.25 0.00 8.00 0.00

Adjustments

Total

72.00 20.25 0.00 8.00 0.00

I have to enter all this manually to get the result I want and figured I could save alot of time by just entering the information in the total hours worked column and have everything else be automated. I am trying to do a calculation for two work weeks so I can calculate the overtime hours separately.

The Column Regular Hours would be 8 hours if the employee works at least 8 hours. If the employee only works 7.99 hours than that should be the number in this column. If the employee works 39.98 hours Sunday-Friday and on Saturday works 3.98. I need it to show .02 hours in this column to only reflect the 40 hour work week and 3.96 would be calculated as overtime.

For the Column Overtime, If the employee works over 8 hours then it would be Total Hours-Regular Hours. Also, anything after the 40 hours in the work week has been complete would be considered overtime. This is the formula I am using for this.

If(E1>0, E1-F1,0). For Saturday, this is the Formula I am using, IF Sum(F1:F7)>40, E1-F1+Sum(F1:F7)-40, E1-F1. So far I haven't run into any problems with the numbers I have sampled but don't know if I will run into problems with certain scenarios.

I am not a wiz with this kind of thing and I know there are many great programmers out there. I would appreciate your help and input on this. Thanks.

I have different timestamps that I need to calculate the time between them

however I need to exclude evenings and weekends.

I'm using the standard NETWORKDAY function which works great for those

countries who work Monday > Friday.

However I need to also calculate the same for those countries working Sun >

Thurs and Sat > Wed

The formula that I have is:

=(NETWORKDAYS(J36,K36)-2)*10/24+((WEEKDAY(J36,2)

tutorial or somewhere where I can learn how to make a sheet where I can put

in the 2 times I punch in everyday, and the 2 times I punch out, having Excel

figure out my total hours and then times it by my payrate?

IE:

I have to punch-in in the morning and then again in the afternoon.

In - 6:00AM

Out - 9:00AM

In - 2:30AM

Out - 5:30AM

Which adds up to 6 hours,

then I want to multiply

6 hours, by $11.00 an hour.

= $66

but I want to know how to make Excel figure out the hours and the total pay

automatically.

How can I do that?

or

Find a tutorial that shows me how to do it.

(do I have to know, and convert the hours to decimals? ) If so how?

E-mail address --