Free Microsoft Excel 2013 Quick Reference

Calculating a colmun to total a 40 hour work week

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 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 need a formula to calculate overtime, after a work week of 40 hours.

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

All,

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

I am trying to total data elements from weekly worksheets to a final work
sheet which will give me quarterly and semiannual totals.

How do I format a cell to display decimal hours?

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

How do I format a cell to display decimal hours?

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

Hi, I am trying to add the hours scheduled to an employee, I am having trouble with the formula to calculate the amount of hours scheduled for the week.
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.

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

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

Hi there,

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 really need some help here!!! I'm battling to come up with the formula to calculate time and half and double time AFTER 40 hours has been worked.
I have attached a copy of my worksheet. Any suggestions would be greatly appreciated.

thanks

DB

I need some help! I need to make a spreadsheet to calculate our weekly time. I have all the data entered and everything is going great until I get to the total hours worked.

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??

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

Hi there,

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!

I am trying to total up hours worked in a one week time period. I found the
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.

i have this formula =D7+(C7>D7)-C7 which will allow me to total up hours worked over a 24 hour period weather it goes into the following day which is great.what i need is to add a formula to that so it will knock off 30 mins if i work over 6 hours in a day and dont knock off 30 mins if i work under 6 hours a day,your help will be much appreciated,thanks.
p.s. i've attached my time sheet.

I have created a Workbook to use for calculating daily and weekly hours. The Workbook consists of eight total sheets, seven of which represent the individual days of the week, and the eighth is used to calculate totals to be sent to accounting for payroll.

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!

Hello.

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 have been working on a time sheet for my company because the one they use is lame.

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.

Hi all,

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

Hi Everyone,

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.

Hi

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)

I want to keep track of my hours, and multiply it by my payrate. Is there a
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 --