Timesheet

[Dannyj01]

Hi all, I'm new to the forum so hi and Thankyou for any advice.

I am currently working on a timesheet for my partner, the problem I have is the way the sheet works I have a column that accumulates any flexi time over 7 and a half hours but she has started doing half days on Wednesday only so I need to try figure out how to manipulate the below code to have full days and half days.

=IF(G5=0,"",(G5-$C$1)+H4)

The $c$1 has 7:30 and it adds the flexi for the day before. If I add a 1pm finish on a Wednesday then it basically removes 4 hours from the accumulated flexi.

Any help is greatly appreciated

 

[Dave Patton]

You did not provide the layout of your data.
Several examples with expected results would be useful.

Excel 2010
	B	C	D	E	F	G	H	I
1	Date	7:30	4:00				Flex Time
2
3							Flex	Regular
4	Mon 03-Sep-18					7:30		7:30
5	Tue 04-Sep-18					8:00	0:30	7:30
6	Wed 05-Sep-18					4:25	0:25	4:00
1c

Cell Formulas
Range	Formula
I4	=G4-H4
H5	=IF((WEEKDAY(B5,2)=3),G5-$D$1,(G5-$C$1))

 

[Dannyj01]

Hi Dave,

I currently have the below.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Standard Hours[/TD]
[TD]7:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Wednesday Half Day[/TD]
[TD]4:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]Date[/TD]
[TD]Morning Start[/TD]
[TD]Morning End[/TD]
[TD]Afternoon Start[/TD]
[TD]Afternoon End[/TD]
[TD]Time Worked[/TD]
[TD]Cumulative[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]27/08/2018[/TD]
[TD]7:00[/TD]
[TD]12:00[/TD]
[TD]12:30[/TD]
[TD]15:00[/TD]
[TD]7:30[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]28/08/2018[/TD]
[TD]7:00[/TD]
[TD]12:00[/TD]
[TD]12:30[/TD]
[TD]16:00[/TD]
[TD]8:30[/TD]
[TD]1:00[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]29/08/2018[/TD]
[TD]7:00[/TD]
[TD]13:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I originally had [TABLE="width: 177"]
<tbody>[TR]
[TD="class: xl65, width: 177"] =IF(G5=0,"",(G5-$C$1)+H4)[/TD]
[/TR]
</tbody>[/TABLE]

in column H to add the cumulative flexi that has been accrued during the week, this works fine everyday if everyday is a standard day of 7:30.

Since my partner has started doing half days on a wednesday, I need to be able to have the function say if column A is = to Wednesday then use 4:00 otherwise use 7:30 which would be C1 and C2.

Much appreciated for the help as this is doing my head in at the minute.

thanks,

Daniel

 

[Dave Patton]

Hello Daniel

The sample below shows 2 alternatives. The first version does not require the text "Wednesday".

Excel 2010
	A	B	C	D	E	F	G	H
1		Standard Hours	7:30
2		Wednesday Half Day	4:00
3	Day	Date	Morning Start	Morning End	Afternoon Start	Afternoon End	Time Worked	Cumulative
4		Mon 27-Aug-18	7:00	12:00	12:30	15:00	7:30	0:00
5		Tue 28-Aug-18	7:00	12:00	12:30	16:00	8:30	1:00
6		Wed 29-Aug-18	7:00	13:00			6:00	3:00
7	or
8	Monday	27-08-18	7:00	12:00	12:30	15:00	7:30	0:00
9	Tuesday	28-08-18	7:00	12:00	12:30	16:00	8:30	1:00
10	Wednesday	29-08-18	7:00	13:00			6:00	3:00
1cc

Cell Formulas
Range	Formula
G4	=D4-C4+F4-E4
H4	=IF((WEEKDAY(B4,2)=3),ROUND(G4-$C$2+N(H3),4),ROUND(G4-$C$1+N(H3),4))
H8	=IF(A8="Wednesday",ROUND(G8-$C$2+N(H7),4),ROUND(G8-$C$1+N(H7),4))