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.

A	B	C	D	E	F	G	H
	Standard Hours	7:30
	Wednesday Half Day	4:00
Day	Date	Morning Start	Morning End	Afternoon Start	Afternoon End	Time Worked	Cumulative
Monday	27/08/2018	7:00	12:00	12:30	15:00	7:30	0.00
Tuesday	28/08/2018	7:00	12:00	12:30	16:00	8:30	1:00
Wednesday	29/08/2018	7:00	13:00				1:00

<tbody>
</tbody>

I originally had

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

<tbody>
</tbody>

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))