Holiday without adding Time in and Time Out

[Robert Wyatt]

I trying to fix my time sheet to add 8 hours holiday time when Holiday is in Column B14:B20 without having to add time in and time out. is that anyway to do this?

New Monthly Time Sheet.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
12	Weekday Date	Options	Shift Starts	Lunch Starts	Lunch Ends	Shift Ends	Worked Hours	Regular Hours	Overtime Hours	Holiday Hours	Vacation Hours	Sick Leave	Personal Leave
13
14	Mon 12/30	Regular	7:00 AM	12:00 PM	12:30 PM	3:30 PM	8.00	8.00	-	-	-	-	-
15	Tue 12/31	Regular	7:00 AM	12:00 PM	12:30 PM	3:30 PM	8.00	8.00	-	-	-	-	-
16	Wed 01/01	Holiday	7:00 AM	12:00 PM	12:30 PM	3:30 PM	-	-	-	8.00	-	-	-
17	Thu 01/02	Regular	7:00 AM	12:00 PM	12:30 PM	3:30 PM	8.00	8.00	-	-	-	-	-
18	Fri 01/03	Regular	7:00 AM	12:00 PM	12:30 PM	3:30 PM	8.00	8.00	-	-	-	-	-
19	Sat 01/04	Off					-	-	-	-	-	-	-
20	Sun 01/05	Off					-	-	-	-	-	-	-
21						Total Weekly Hours:	32.00	32.00	-	8.00	-	-	-
Time Sheet

Cell Formulas
Range	Formula
G14:G20	G14	=IF(B14="Regular",(MOD((TIME(HOUR(F14),MROUND(MINUTE(F14),15),0)-TIME(HOUR(C14),MROUND(MINUTE(C14),15),0)),1)*24)-MOD((TIME(HOUR(E14),MROUND(MINUTE(E14),15),0)-TIME(HOUR(D14),MROUND(MINUTE(D14),15),0)),1)*24,0)
H14:H20	H14	=G14-I14
I14:I20	I14	=ROUND(MAX(IF('Data Sheet'!$B$5,MAX(0,SUM(H$13:H13)+G14-'Data Sheet'!$B$6),0),IF('Data Sheet'!$B$2,IF(G14>'Data Sheet'!$B$3,G14-'Data Sheet'!$B$3,0),0)),2)
J14:J20	J14	=IF(B14="Holiday",(MOD((TIME(HOUR(F14),MROUND(MINUTE(F14),15),0)-TIME(HOUR(C14),MROUND(MINUTE(C14),15),0)),1)*24)-MOD((TIME(HOUR(E14),MROUND(MINUTE(E14),15),0)-TIME(HOUR(D14),MROUND(MINUTE(D14),15),0)),1)*24,0)
K14:K20	K14	=IF(B14="Vactaion",(MOD((TIME(HOUR(F14),MROUND(MINUTE(F14),15),0)-TIME(HOUR(C14),MROUND(MINUTE(C14),15),0)),1)*24)-MOD((TIME(HOUR(E14),MROUND(MINUTE(E14),15),0)-TIME(HOUR(D14),MROUND(MINUTE(D14),15),0)),1)*24,0)
L14:L20	L14	=IF(B14="Sick",(MOD((TIME(HOUR(F14),MROUND(MINUTE(F14),15),0)-TIME(HOUR(C14),MROUND(MINUTE(C14),15),0)),1)*24)-MOD((TIME(HOUR(E14),MROUND(MINUTE(E14),15),0)-TIME(HOUR(D14),MROUND(MINUTE(D14),15),0)),1)*24,0)
M14:M20	M14	=IF(B14="Personal",(MOD((TIME(HOUR(F14),MROUND(MINUTE(F14),15),0)-TIME(HOUR(C14),MROUND(MINUTE(C14),15),0)),1)*24)-MOD((TIME(HOUR(E14),MROUND(MINUTE(E14),15),0)-TIME(HOUR(D14),MROUND(MINUTE(D14),15),0)),1)*24,0)
G21:M21	G21	=SUM(G14:G20)

Cells with Data Validation
Cell	Allow	Criteria
B14:B20	List	Off,Regular,Holiday,Vacation,Sick,Personal

 

[Peter_SSs]

It is not at all clear to me what you want or where. This is my best guess.
If this is not what you mean we would need much clearer detail of what you want, where it should go, and what logic produces it.

25 02 23.xlsm
	B	C	D	E	F				J
12	Options	Shift Starts	Lunch Starts	Lunch Ends	Shift Ends				Holiday Hours
13
14	Regular	7:00:00 AM	12:00:00 PM	12:30:00 PM	3:30:00 PM				0:00
15	Regular	7:00:00 AM	12:00:00 PM	12:30:00 PM	3:30:00 PM				0:00
16	Holiday								8:00
17	Regular	7:00:00 AM	12:00:00 PM	12:30:00 PM	3:30:00 PM				0:00
18	Regular	7:00:00 AM	12:00:00 PM	12:30:00 PM	3:30:00 PM				0:00
19	Off								0:00
20	Off								0:00
Holiday Hrs

Cell Formulas
Range	Formula
J14:J20	J14	=IF(B14="Holiday",1/3,0)

 

[Robert Wyatt]

It works wonderfully. this was just what i needed. thank you so much for the help.

 

[Peter_SSs]

You're welcome. Glad it was a lucky guess on my part.
Thanks for the follow-up.