Need the right Formula

[ggwynn]

i'm having troubles with the if function of excel and already spent many hours but still no positive result,i want to make a record of my overtimes daily,what is the right formula in excel,the conditions are as follow:

Monday to Friday(total work hours-8= overtime)
Saturday(total work hours-4= Overtime)
Sunday(total work hours=overtime)
please help.thank you

 

[scottylad2]

i'm having troubles with the if function of excel and already spent many hours but still no positive result,i want to make a record of my overtimes daily,what is the right formula in excel,the conditions are as follow:

Monday to Friday(total work hours-8= overtime)
Saturday(total work hours-4= Overtime)
Sunday(total work hours=overtime)
please help.thank you

What is the minus doing?not entirely clear what your layout is or what your trying to show. is it anything over the stated daily 8 hours and 4 hours for Sat?

 

[MrKowz]

Welcome to the forums!

Need something like this?

Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Date</TD><TD>Hours Worked</TD><TD>Hours Overtime</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">Sun, 03/13/11</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">Mon, 03/14/11</TD><TD style="TEXT-ALIGN: right">7.5</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">Tue, 03/15/11</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">Wed, 03/16/11</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">Thu, 03/17/11</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">Fri, 03/18/11</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">Sat, 03/19/11</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP: black 1px solid">Total</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right">50.5</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right">7</TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet3</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C2</TH><TD style="TEXT-ALIGN: left">=MAX(B2-LOOKUP(WEEKDAY(A2),{1,2,3,4,5,6,7},{0,8,8,8,8,8,4}),0)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B9</TH><TD style="TEXT-ALIGN: left">=SUM(B2:B8)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C9</TH><TD style="TEXT-ALIGN: left">=SUM(C2:C8)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[ggwynn]

not really like that,i have a format but i can't post it..hmmm i wonder why..thanks for the welcome

 

[MrKowz]

not really like that,i have a format but i can't post it..hmmm i wonder why..thanks for the welcome

If you can describe the layout of your data, we might be able to tailor a formula to your needs.

 

[ggwynn]

well...
1st column is the date, i.e 1-31
2nd column is the day, i.e Sunday-Saturday,
3rd column to 26th column is the time, i.e 0000h-2300h military time
27th column is the total work hours
28th column is the Over time hours
i'll have to tally every hour,,what i really need is the "if formula"..i guess this will be easier if i can provide a picture..

 

[ggwynn]

hey,,here it is,
the value of "/" and "\" is 0.5
the value of "X" is 1

i want to make the formula in the "Total O/T" column
this is my formula for Monday to Friday =IF(AA17>0,ABS(AA17-8),0)
but i still have Saturdays and Sundays
during saturday i have to work 4 hours and the excess hours will be overtime, sundays are overtime

sorry for bothering you guys

 

[MrKowz]

hey,,here it is,
the value of "/" and "\" is 0.5
the value of "X" is 1

i want to make the formula in the "Total O/T" column
this is my formula for Monday to Friday =IF(AA17>0,ABS(AA17-8),0)
but i still have Saturdays and Sundays
during saturday i have to work 4 hours and the excess hours will be overtime, sundays are overtime

sorry for bothering you guys

Try uploading the picture to a free image sharing site like photobucket or imageshack.

And don't worry about "bothering" us, we're all volunteers here.

 

[jasonb75]

Maybe this will work for you

In the first column, enter your dates as regional format i.e. for UK dd/mm/yyyy this way, excel will recognise them as actual dates.

In AA17 =COUNTIF(C17:Z17,"X")+(COUNTIF(C17:Z17,"\")+COUNTIF(C17:Z17,"/"))/2
In AB17 =MAX(0,AA17-LOOKUP(WEEKDAY(A17,3),{0,5,6},{8,4,0}))

Hope this helps.

 

[scottylad2]

Excel Workbook
	A	B	C	D	E	F
2	Date	Day	Start	End	Hours	OT Hours
3	01/02/2011	Tue	18:00	03:00	09:00	01:00
4	02/02/2011	Wed	18:00	03:00	09:00	01:00
5	03/02/2011	Thu	18:00	02:00	08:00	00:00
6	04/02/2011	Fri	18:00	02:00	08:00	00:00
7	05/02/2011	Sat	14:00	19:00	05:00	01:00
8	06/02/2011	Sun	10:00	15:00	05:00	05:00
9	07/02/2011	Mon	18:00	05:00	11:00	03:00
10	08/02/2011	Tue	18:00	03:00	09:00	01:00
11	09/02/2011	Wed	18:00	02:00	08:00	00:00
12	10/02/2011	Thu	18:00	02:00	08:00	00:00
13	11/02/2011	Fri	14:00	19:00	05:00	00:00
14	12/02/2011	Sat	10:00	15:00	05:00	01:00
15	13/02/2011	Sun	18:00	02:00	08:00	08:00
16	14/02/2011	Mon	18:00	03:00	09:00	01:00
17	15/02/2011	Tue	18:00	02:00	08:00	00:00
18	16/02/2011	Wed	18:00	02:00	08:00	00:00
19					Total O/T	22:00
Sheet4

Excel 2010

Cell Formulas
Range	Formula
B3	=A3
E3	=MOD(D3-C3,1)
F3	=IF(WEEKDAY(A3,2)=6,MAX(TIMEVALUE("4:00"),E3)-TIMEVALUE("4:00"),IF(WEEKDAY(A3,2)=7,E3,MAX(TIMEVALUE("8:00"),E3)-TIMEVALUE("8:00")))