[Shokra]

I am creating a Wages, Hour and Payroll summary for my fiancé because he's trying to keep track how the new company is handling wages/time schedules, etc.

However, I'm struggling with a few calculations that I just cannot find solutions for online.

Basically my sheet is set to:
F G
10 Hours Overtime
11 Monday 8.5
12 Tuesday 8.5
13 Wednesday 9.5
14 Thursday 12.5
15 Friday 9.5
16 Saturday
17 Sunday
18 Total Wkly Hours:____ ____

The formula that I have in F-18 is as follows: =((F12-0.5)+(F13-0.5)+(F14-0.5)+(F15-0.5)+(F16-0.5)+(F17-0.5)+(F18-0.5))
It totals the hours he works then minus' his non-paid 30 min break
The problem that I am running into is:
1 - Saturday & Sunday - I want the formula to bypass these cells (F16 & F17) if they are blank

Now for the Overtime column (G11):
Basically I have this formulated to calculate his hours worked minus his non-paid break, then determine anything over the figure in F11 (formula: =(F12-8.5)
but if the F column has no figures, it is still calculated and gives the G column a negative value :/

G18 just sums the figures in the G column and gives him a weekly total of overtime that can be applied to personal and/or holidays.

The problem is, if cell F16 and/or F17 are blank they are still computing the formula, I want it to ignore what is NULL/Blank

I know my formulas aren't very complex and could probably be done much better, but I am not an expert nor every day user of Excel. I can normally make calculations do what I want them to do just by googling information but in this case.. I cant, so if someone could please help me I would greatly appreciate it.

Thank you
Sho

[Claus Busch]

Hi,

Am Fri, 9 Nov 2012 18:45:39 +0000 schrieb Shokra:

Basically my sheet is set to:
F G
10 Hours Overtime
11 Monday 8.5
12 Tuesday 8.5
13 Wednesday 9.5
14 Thursday 12.5
15 Friday 9.5
16 Saturday
17 Sunday
18 Total Wkly Hours:____ ____

in F18 try:
=SUM(F11:F17)-COUNT(F11:F17)*0.5

in G11:
=IF(F11="",0,F11-8.5)
and copy down


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Shokra]

Thank you Claus for your reply, I will definate try those after a few hours of sleep. However, most of the night I have recreated the formulas and the following is now what I am working with. It would probably give much better details, etc.

Should have provided a bit more information before I tried to explain the formula calculations problems I am having. I thought I could attach the actual spreadsheet but I can't - I will try to explain each column and what I am "trying" to accomplish, maybe someone would be kind enough to assist me with my horrible mistakes. (Attached a PDF of the actually sheet).

As you can see I am creating a salary/payroll w/ hours, breaks and over hour calculation (only). Reason being is that overtime is not paid via time and half, but those accumolated hours become avialable to use for personal/holiday time off w/ pay (similar to "floating hours").

Description of Sheet & Calculations:

C9 thru L9
Each week of the A2 year/month & A9 beginging & ending wk day(s) of the months pay period
A10-16 Days of Wk

Week 1 = work days from 29.10 - 04.11
C10 = 8.5 hrs wk
C11 = 8.5 hrs wk
C12 = 8.5 hrs wk
C13 = 8.5 hrs wk
C14 = 8.16 hrs wk (8 hrs 10 mins)
C15 = off
C16 = off

C17's formula is: =((C10-0.5)+(C11-0.5)+(C12-0.5)+(C13-0.5)+(C14-0.5)+(C15-0.5)+(C16-0.5))
It works - sort of! Basically I want it to add up add C10-C16, if the work day is blank, it ignore that cell, then based on how many cells have hrs entered it #ofcells *0.5 to obtain the hours of break time (unpaid), then breakhrs - Wk hrs = Total wkly hrs

However occassional, he works Saturday for a few hours and doesn't get a 30 min (unpaid) break - How can I calculate unpaid breaks to specific days only?
Example: Sum((C10:C16)-(#ofcells*0.5))

I'm sure there is a formula out there that can do this unfortunately, I have not been able to fine one (they all do things I don't want them to do). My current formula does work, but I think it may be what is causing the error I receive when I reopen the document (see final error at the end of thread).
____________________________
D10 thru D16: calculates if the hours are more then reg scheduled hrs it shows the overtime hours.

D10:D16's formulas a
Mon - Thurs is: =IF(C108.5,(C10-8.5),0)
Fridays is: =IF(C148.16,(C14-8.16),0)

This took some time but I did get it working but only for Monday - Thursday. Once I apply the same concept to Friday (you see I manual changed the hours), it shows an error.

Error: The formula in this cell differs from the formulas in this area of the spreadsheet.
I can't figure out why

E-L #'s10-16 are just same as what is described above.
____________________________

Overtime Column:

D17's formula is: =SUM(D10:D16)
However, its not calculating and states: The formula in this cell differs from the formulas in this area of the spreadsheet.
What am I doing wrong?

Everything else on the spreadsheet regarding his payroll is functioning properly, to my knowledge.

Reopen File Error:
As soon as I open the document I get the following:
Circular Reference Warning:
One or more formulas contain a circular reference and may not calculate
correctly. Circular references are any references within a formula that
depend upon the results of that same formula. For example, a cell that refers
to its own value or a cell that refers to another cell which depends on the
original cell's value both contain circular references.
For more information about understanding, finding, and removing circular
references, click ok. If you wnat to create a circular reference, click
cancel to continue.

I have spent quite a while trying to figure out which formula is creating this error with no success. I really appreciate the time anyone takes to read my issues and assists with the problems.

Thank you
Sho

Originally Posted by Claus Busch

Hi,

Am Fri, 9 Nov 2012 18:45:39 +0000 schrieb Shokra:

Basically my sheet is set to:
F G
10 Hours Overtime
11 Monday 8.5
12 Tuesday 8.5
13 Wednesday 9.5
14 Thursday 12.5
15 Friday 9.5
16 Saturday
17 Sunday
18 Total Wkly Hours:____ ____

in F18 try:
=SUM(F11:F17)-COUNT(F11:F17)*0.5

in G11:
=IF(F11="",0,F11-8.5)
and copy down


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Claus Busch]

Hi Shokra,

Am Sat, 10 Nov 2012 03:57:16 +0000 schrieb Shokra:

Description of Sheet & Calculations:

C9 thru L9
Each week of the A2 year/month & A9 beginging & ending wk day(s) of the
months pay period
A10-16 Days of Wk

Week 1 = work days from 29.10 - 04.11
C10 = 8.5 hrs wk
C11 = 8.5 hrs wk
C12 = 8.5 hrs wk
C13 = 8.5 hrs wk
C14 = 8.16 hrs wk (8 hrs 10 mins)
C15 = off
C16 = off

C17's formula is:
=((C10-0.5)+(C11-0.5)+(C12-0.5)+(C13-0.5)+(C14-0.5)+(C15-0.5)+(C16-0.5))
It works - sort of! Basically I want it to add up add C10-C16, if the
work day is blank, it ignore that cell, then based on how many cells
have hrs entered it #ofcells *0.5 to obtain the hours of break time
(unpaid), then breakhrs - Wk hrs = Total wkly hrs

you can't substract unpaid breaks if there is no worktime. If you only
get unpaid breaks with 6 or more hours of work then try in C17:
=SUM(C10:C16)-COUNTIF(C10:C16,""&6)*0.5
In other case modify to suit.
Every time over 8.5 hours is overtime. Then in D10:
=IF(C10="",0,C10-8.5) and copy down. D10 to D13 = 0
D14 = -0.34 because she worked less than 8.5 hours. D15 and D16 = 0
because she didn't work.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Kevin@Radstock]

Hi

Probably simpler and save on all that typing if you uploaded a sample spreadsheet (Dummy data)

Originally Posted by Shokra

Thank you Claus for your reply, I will definate try those after a few hours of sleep. However, most of the night I have recreated the formulas and the following is now what I am working with. It would probably give much better details, etc.

Should have provided a bit more information before I tried to explain the formula calculations problems I am having. I thought I could attach the actual spreadsheet but I can't - I will try to explain each column and what I am "trying" to accomplish, maybe someone would be kind enough to assist me with my horrible mistakes. (Attached a PDF of the actually sheet).

As you can see I am creating a salary/payroll w/ hours, breaks and over hour calculation (only). Reason being is that overtime is not paid via time and half, but those accumolated hours become avialable to use for personal/holiday time off w/ pay (similar to "floating hours").

Description of Sheet & Calculations:

C9 thru L9
Each week of the A2 year/month & A9 beginging & ending wk day(s) of the months pay period
A10-16 Days of Wk

Week 1 = work days from 29.10 - 04.11
C10 = 8.5 hrs wk
C11 = 8.5 hrs wk
C12 = 8.5 hrs wk
C13 = 8.5 hrs wk
C14 = 8.16 hrs wk (8 hrs 10 mins)
C15 = off
C16 = off

C17's formula is: =((C10-0.5)+(C11-0.5)+(C12-0.5)+(C13-0.5)+(C14-0.5)+(C15-0.5)+(C16-0.5))
It works - sort of! Basically I want it to add up add C10-C16, if the work day is blank, it ignore that cell, then based on how many cells have hrs entered it #ofcells *0.5 to obtain the hours of break time (unpaid), then breakhrs - Wk hrs = Total wkly hrs

However occassional, he works Saturday for a few hours and doesn't get a 30 min (unpaid) break - How can I calculate unpaid breaks to specific days only?
Example: Sum((C10:C16)-(#ofcells*0.5))

I'm sure there is a formula out there that can do this unfortunately, I have not been able to fine one (they all do things I don't want them to do). My current formula does work, but I think it may be what is causing the error I receive when I reopen the document (see final error at the end of thread).
____________________________
D10 thru D16: calculates if the hours are more then reg scheduled hrs it shows the overtime hours.

D10:D16's formulas a
Mon - Thurs is: =IF(C108.5,(C10-8.5),0)
Fridays is: =IF(C148.16,(C14-8.16),0)

This took some time but I did get it working but only for Monday - Thursday. Once I apply the same concept to Friday (you see I manual changed the hours), it shows an error.

Error: The formula in this cell differs from the formulas in this area of the spreadsheet.
I can't figure out why

E-L #'s10-16 are just same as what is described above.
____________________________

Overtime Column:

D17's formula is: =SUM(D10:D16)
However, its not calculating and states: The formula in this cell differs from the formulas in this area of the spreadsheet.
What am I doing wrong?

Everything else on the spreadsheet regarding his payroll is functioning properly, to my knowledge.

Reopen File Error:
As soon as I open the document I get the following:
Circular Reference Warning:
One or more formulas contain a circular reference and may not calculate
correctly. Circular references are any references within a formula that
depend upon the results of that same formula. For example, a cell that refers
to its own value or a cell that refers to another cell which depends on the
original cell's value both contain circular references.
For more information about understanding, finding, and removing circular
references, click ok. If you wnat to create a circular reference, click
cancel to continue.

I have spent quite a while trying to figure out which formula is creating this error with no success. I really appreciate the time anyone takes to read my issues and assists with the problems.

Thank you
Sho

[Shokra]

Originally Posted by Kevin@Radstock

Hi

Probably simpler and save on all that typing if you uploaded a sample spreadsheet (Dummy data)

Ive tried it wont allow me to upload it, not sure why.. I saved the speadsheet as pdf and its only 1 page but states it too large - Every time I attempt its stating it is too large.. pdf file, even a zipped file.. I'll try to upload it to the server and add a link in a whiles

[joeu2004[_2_]]

"Shokra" wrote:
Kevin@Radstock;1607266 Wrote:
Probably simpler and save on all that typing if you
uploaded a sample spreadsheet (Dummy data)

Ive tried it wont allow me to upload it, not sure why.
I saved the speadsheet as pdf

No need to save as PDF. Probably better if you don't.

I have seen Excel files archived in zip files "attached" to postings
submitted through excelbanter.com. But I am not an ExcelBanter user, so I
cannot help you with that.

However, in general, you can upload an example Excel file (devoid of any
private data) that demonstrates the problem to any file-sharing website.

Then post the "shared", "public" or "view-only" link (aka URL; http://...)
in a response here.

The following is a list of some free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidSha http://www.rapidshare.com

[Shokra]

Claus - your formula is absolutely brilliant! I applied =SUM(C10:C16)-COUNTIF(C10:C16,""&6)*0.5 to cells C17 to K17.. I did attach the excel sheet zipped but I can't remember now if it was before I made those changes or not.

There is one problem with the formula though :( That is, it is not removing the overtime hours. There is no overtime, it becomes like hmm "floating hours". So when I applied the formula to E17, it showed his paid hours as 43, when technically if you minus the break & the overtime, he only has 39.

So when it calculates his gross salary, its adding in those 4 extra hours.
I changed your formula to: =SUM(E10:E16)-COUNTIF(E10:E16,""&6)*0.5 - (F17) and it seems to function properly. What do you think of the above, is it correctly done?

I have two questions:

1 - Overtime column(Friday).. formula is: =IF(C148.16,(C14-8.16),"")
next weeks Friday is: =IF(E147.5,(E14-7.5),"")
These days alternate each week, but always the same. I get the same error on both: Error: The formula in this cell differs from the formulas in this area of the spreadsheet. Is this actually an error of its it just a notification?

2 - When I sum the overtime columns to total in D17, etc, I get the Error: The formula in this cell differs from the formulas in this area of the spreadsheet. Is this actually an error of its it just a notification?

well I'm going to try the zipped file again hope it works this time. :)

[Claus Busch]

Hi Shokra,

Am Sun, 11 Nov 2012 01:54:16 +0000 schrieb Shokra:

There is one problem with the formula though :( That is, it is not
removing the overtime hours. There is no overtime, it becomes like hmm
"floating hours". So when I applied the formula to E17, it showed his
paid hours as 43, when technically if you minus the break & the
overtime, he only has 39.

what are the regular working hours for each day? Is working on weekend
completely overtime?


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2