Another timesheet formual question.

[bajra]

I have a basic working knowledge of excel (using excel 2010). I'm trying to create a timesheet. It needs to have room for 2 breaks and a lunch. here's what i have
=(J4-C4)-(G4-F4)-(E4-D4)-(I4-H4)

My problem is with the 2 breaks (e4-d4 & i4-h4) i need it to calculate if the break is longer than 10 minutes, to just subtract anything in excess of ten minutes for each break. If this has already been covered, if someone could point me in the right direction, my search powers have escaped me the past couple days.

 

[scottylad2]

Actually you might be able to lose the Or part altogether, try altering the If to say if shift end ="" leave ""..... That way you shouldn't get anything showing till the shift end is in place.

On my phone and not near Excel right now, hope it helps

 

[scottylad2]

Excel Workbook
	K	L	M	N	O	P	Q	R	S	T	U
25	Day	Date	Start	Break Out	Break in	Lunch start	lunch end	Break start	Break end	Finnish	Total
26	Friday	01/04/2011	08:00	09:00	09:10	12:00	13:00	15:00	15:10	17:00	08:00
27
28					00:10
Sheet2

A normal day, standard 10 min breaks

With just the start time, no #####signs

Excel Workbook
	K	L	M	N	O	P	Q	R	S	T	U
25	Day	Date	Start	Break Out	Break in	Lunch start	lunch end	Break start	Break end	Finnish	Total
26	Friday	01/04/2011	08:00
Sheet2

Still no ##### signs

Excel Workbook
	K	L	M	N	O	P	Q	R	S	T	U
25	Day	Date	Start	Break Out	Break in	Lunch start	lunch end	Break start	Break end	Finnish	Total
26	Friday	01/04/2011	08:00	09:00	09:15	12:00	13:00	15:00	15:22
Sheet2

A complete day with an extra long couple of breaks, looks like it works as you want now

Excel Workbook
	K	L	M	N	O	P	Q	R	S	T	U
25	Day	Date	Start	Break Out	Break in	Lunch start	lunch end	Break start	Break end	Finnish	Total
26	Friday	01/04/2011	08:00	09:00	09:15	12:00	13:00	15:00	15:22	17:00	07:43
Sheet2

I've put the 00:10 min break in O28 you can hide it wherever you like

 

[bajra]

Excelsior!!! That's done the trick. now reporting time beatifully no ####. that and switching my date calculations of 1904. now my OT shows negative times so i know when i'm under! i'm all set now. thanks for you all your help. It's like magic!