VBA or Excel – Time sheet with RT / OT based on the day of the week.

[JDSOuth49]

I need help with VBA Code or an Excel formula.

PURPOSE: I need to auto calculate OT based on the days of the week (Saturday & Sunday).

For Monday – Friday any hours as follows: [**This part of the sheet works fine]

Cell Range = F15,P15:F50,P50

In Cell Q15 formula: =IF(S15<8,S15,8)

In Cell R15 formula: =IF(S15-8>0,(S15-8),0)

In Cell S15 formula: =SUM(F15:P15)

For Saturday and Sunday Only: [This is where I am having my Issue]

I am wanting to have ANY time on Saturday or Sunday Counted as “Overtime Only” {Column R, Rows 15 to 50}

Cell “C5” is set to return the Day of the weeks using the following formula: =IF(C6="","",TEXT(C6,"dddd"))


Any help would be greatly appreciated

 

[awoohaw]

Can this help you some:

Use the Weekday function.

Book1
	A	B	C	D	E	F
1
2
3
4		Day		Hours Worked	Reg Pay	O/T Pay
5		Sat 24/2/10	6	8	0	8
6		Sun 24/3/10	7	8	0	8
7		Wed 24/4/10	3	8	8	0
8		Fri 24/5/10	5	8	8	0
9		Mon 24/6/10	1	8	8	0
10		Wed 24/7/10	3	8	8	0
11		Sat 24/8/10	6	8	0	8
12		Tue 24/9/10	2	8	8	0
Sheet4

Cell Formulas
Range	Formula
C5:C12	C5	=WEEKDAY(B5,2)
E5:E12	E5	=IF(WEEKDAY($B5,2)>5,0,$D5)
F5:F12	F5	=IF(WEEKDAY($B5,2)<=5,0,$D5)
B6:B12	B6	=EDATE(B5,1)

 

[JDSOuth49]

Thank you for the suggestion, but it interferes with the following formulas:

In Cell Q15 formula: =IF(S15<8,S15,8)
In Cell R15 formula: =IF(S15-8>0,(S15-8),0)
In Cell S15 formula: =SUM(F15:P15)

and I do not know how to get around that.

 

[awoohaw]

I was not telling you to replace entiredly, but to use it in your formulas.
Look at this:
NOTE: This is using Date values in Column C, not a text version of a date (the cells are formatted dates, formats of dddd or dddd yyyy-mm-dd).
(Edited at 11:29, I did not put the 2nd argument in the Weekday Function in the original).

Book1
			C			F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2
3
4
5			Saturday
6			Saturday 2024-01-20
7
8
9
10
11
12
13
14						HRS	HRS	HRS	HRS	HRS	HRS	HRS	HRS	HRS	HRS	HRS	Reg	O/T	Total
15								1	1	1	1	1	1	1	1	1	0	9	9
16			Testing Random Days
17			Saturday 2024-01-20					1	1	1	1	1	1				0	6	6
18			Sunday 2024-01-21					1	1	1	1	1	1	1	1	1	0	9	9
19			Monday 2024-01-22					1	1	1	1	1	1				6	0	6
20			Tuesday 2024-01-23				1	1	1	1	1	1	1	1	1	1	8	2	10
21			Wednesday 2024-01-24								1	1	1	1			4	0	4
22			Thursday 2024-01-25							1	1	1	1				4	0	4
23			Friday 2024-01-26					1	1	1	1	1	1	1	1		8	0	8
24			Saturday 2024-01-27					1	1	1	1	1	1	1	1	1	0	9	9
25			Sunday 2024-01-28							1	1	1	1				0	4	4
26			Monday 2024-01-29										1	1	1	1	4	0	4
27			Tuesday 2024-01-30										1	1	1	1	4	0	4
28			Wednesday 2024-01-31				1	1	1	1	1	1	1	1	1	1	8	2	10
29			Thursday 2024-02-01														0	0	0
Sheet1

Cell Formulas
Range	Formula
C5	C5	=C6
Q15	Q15	=IF(WEEKDAY(C5,2)<=5,IF(S15>8,8,S15),0)
R15	R15	=IF(WEEKDAY(C5,2)>5,S15,IF(S15>8,S15-8,0))
S15	S15	=SUM($F$15:$P$15)
Q17:Q29	Q17	=IF(WEEKDAY(C17,2)<=5,IF(S17>8,8,S17),0)
R17:R29	R17	=IF(WEEKDAY(C17,2)>5,S17,IF(S17>8,S17-8,0))
S17:S29	S17	=SUM($F17:$P17)
C18:C29	C18	=C17+1

 

[JDSOuth49]

Thank you. this worked

 

[awoohaw]

Happy to have helped. Best Wishes!