Help with formula

[bizzilizzi]

Hallo

I am new to this forum and also very new to formulas in excel. I search the net, found many timesheets but not with what I am trying to do. I am struggling and need some help.
In Sheet A
I have in sheet A (named "Lists") in column "O" a list of public holidays -ranges 2 to 11, the range is called Pub_Hol.
In Sheet B
Sheet B (named "Week 4"), I have
column B - Date (date format - 06/05/2011)
column E - Start Time, (hh:mm)
column F - End Time, (hh:mm)
column G - Total Reg Hrs, (hh:mm) (=IF(COUNT(E2:F2)=2, F2-E2+(E2>F2), "")
column H, Total Pub Hrs (hh:mm)

What I want to achieve is the following:

When I enter a public holiday date on Sheet B, column B which correspond to one of the dates in Sheet A, column O - I want that dates to come up in a different colour/or highlighted (if possible). Secondly should I enter one of these public holidays in column B on sheet B the total hours in column G should be disabled. Thirdly the calculation of the total hours for column H (Pub Hrs) should then be activated and multiplied by 1.5 ( that is column F minus column E mulitiplied by 1.5)

I need to repeat this formula on new sheets for every week.

I hope it makes sense

Any help will be greatly appreciated.

 

[Akashwani]

Hi bizzilizzi,

Welcome to MrExcel.

See if this Monthly Time Sheet is similar to what you want to do then perhaps we can work from there....

MonthlyTimeSheet3.xls

Ak

 

[bizzilizzi]

Hi Ak,

That is a very nice timesheet and I might be able to use some of the features. In my timesheet I have the following:

Sheet 1 - All the staff with their details, pay rates, and a summary of their weekly pay, holiday pay and overtime.
Sheet 2 - All my lists I refer back to - just to make it easy for myself
Sheet 3 - Clients -with description of the different shifts provided, regular rates and public holiday rates
Sheet 4 - The bookings - (A)with client name, (B)day) eg WED, (C) date (dd:mm:yy) (D)description of the shift that has been booked, (E)the staff member name, (F)start time, (G)end time, (H)total regular hours, (I)total public holiday time, (J)breaks, (K)total hours, (L)client rate, (M)line total

Column C, D E, K, L and M I copy to another spreadsheet which is my invoice.

Most of my calculations work and some are updated in sheet A, however, I am by no way an expert and I am at a very frustrating point not understanding some of the functions and how to make it work. Any help to automate some of the functions would be much appreciated.

 

[Akashwani]

Hi,

Well that's a bit different to your original post

I'm not really sure what you require now

Here is a Weekly Time Sheet see if you can use/modify it...


Weekly Time Sheet.xls


It would be best if you could post a sample of your sheet(s) and the expected results you require, something like this perhaps...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	",CELL("filename",A1))+1,256)]Week18	Date			Start Time	End Time	Total Reg Hrs	Total Pub Hrs			OT Rate		2011
2	18	02/05/2011			08:00	16:00		12:00			1.5
3		03/05/2011			08:00	16:00	8:00
4		04/05/2011			08:00	14:00	6:00
5		05/05/2011			06:00	17:00	11:00
6		06/05/2011
7		07/05/2011
8		08/05/2011
Week18

The formulas in B3, G2, H2 need to be copied down.
If you can post a link to your file I will take a look at it.

Ak

 

[bizzilizzi]

Hi Ak,

Many thanks, I am going to see if I can work with it. I really appreciate your help and will get back to you - I am sure with more questions.

 

[Akashwani]

No problem.

Is this something like you require?..

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	",CELL("filename",A1))+1,256)]Week18	Day	Date	Shift	Staff Name	Start Time	End Time	Total Reg Hrs	Total Hol Hrs	Break	Total Hrs	Client	Total Pay			2011
2	18	Mon	02/05/2011	6-2	Bob	06:00	14:00		12:00	1:30	10:30		72.98			OT Rate
3		Tue	03/05/2011	6-2	Bob	06:00	14:00	8:00		1:00	7:00		48.65			1.5
4		Wed	04/05/2011
5		Thu	05/05/2011
6		Fri	06/05/2011
7		Sat	07/05/2011
8		Sun	08/05/2011
Week18

Take a look here...

Weekly Time Sheet2.xls


Good luck.

Ak

 

[bizzilizzi]

Hi Ak,

I am struggling and will attach the file for you to see. I just need to make a copy and remove some info.

Again thanks for your help!

 

[bizzilizzi]

Hi Ak,

Here is the link to the file

http://www.4shared.com/file/C-b6pBkR/Timesheet_Working_Doc.html

I will continue tomorrow and give all the functions you gave me a try.

Cheers

 

[Akashwani]

Hi,

I have looked at your Workbook and added some formulas, probably not the best, but they do seem to work. Here is a snap shot of what I have done, the cells highlighted blue are where I have added formulas.
I've changed some of the names to protect the innocent or guilty

Excel Workbook
	A	B	C	D	E	F	G
1	Vlookup Helper Column	Shift	Client	Rate HCA	Pub Hol Rate HCA	Rate RGN	Pub Hol Rate RGN
2	*	*	*	*	*	*	*
3	Company A-Week Early Shift	Week Early Shift	Company A	20.00	40.00	36.00	72.00
4	Company A-Week Late Shift	Week Late Shift	Company A	24.00	48.00	36.00	72.00
5	Company A-Week Night Shift	Week Night Shift	Company A	26.00	52.00	36.00	72.00
6	Company A-Weekend Long Day Shift	Weekend Long Day Shift	Company A	24.00	48.00	40.00	80.00
7	Company A-Weekend Early Shift	Weekend Early Shift	Company A	22.00	44.00	38.00	76.00
8	Company A-Weekend Late Shift	Weekend Late Shift	Company A	28.00	56.00	42.00	84.00
9	Company A-Weekend Night Shift	Weekend Night Shift	Company A	30.00	60.00	45.00	90.00
Descriptions & Rates

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	CLIENT	DATE	DESCRIPTION	STAFF	STARTING TIME	ENDING TIME	REG HRS	PUB HRS	BREAKS	HOURS	UNIT PRICE	LINE TOTAL
2	Company A	27/04/2011	Week Early Shift	HCA John Smith	08:00	20:00	12:00	*	*	12:00	20.00	240.00
3	Company A	29/04/2011	Week Late Shift	HCA John Doe	08:00	20:00	*	12:00	*	12:00	48.00	576.00
4	Company A	29/04/2011	Week Night Shift	RGN Jo Doe	20:00	08:00	*	12:00	*	12:00	72.00	864.00
5	Company A	30/04/2011	Weekend Long Day Shift	HCA John Smith	08:00	20:00	12:00	*	*	12:00	24.00	288.00
6	Company A	30/04/2011	Weekend Late Shift	HCA Jan Doe	08:00	20:00	12:00	*	*	12:00	28.00	336.00
7	*	*	*	*	*	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*	*	*	*	*	*
Week 4

I'm sure there must be a better way to return the correct hourly rate in column K than the one I have used, but that is all I have, sorry.

I hope this helps resolve your problem, good luck.

AK

 

[bizzilizzi]

Hi Ak,

Thanks you are a star!

I can work with the formulas you have sent me.

Thumbs up for the work you have done!