Tech Support Guy banner
Status
Not open for further replies.
1 - 7 of 7 Posts

·
Registered
Joined
·
84 Posts
Discussion Starter · #1 ·
I have got a document in excel that records the hours that agency work for us, what i want to be able to do is put a formula in to calculate the cost.
But each agency have a different rate, and also they have a different rate for nights and weekends, So if an agency started at say 16:00 he would be on day rate up until 18:00 then he goes on to night rate, so it needs to be able to work out when to change the rate.
Would it be easier to put all the information about the rates in a separate sheet or the relevant information on the same sheet as the agency.
if i need to set a vlookup table how would i do that.
in col 1 i have date, col 2, name, col 3, start time, col 4 finish time, col 5 total hours worked (this is -45 min for a break that we don't pay them for) col 6 overtime worked, col 7 cost (Which is where i am having the trouble).
Each agency has a different night start time, some start from 16:00, and some start from 18:00, but they all finish at 6:00.
how would it calculate say if you started at 15:00, your night rate started at 18:00, it would be 3 hours at day rate and the rest at night rate, is there a formula that could break it down like that.
And also on a weekend is there a formula that would be able to tell it was a weekend by the date entered in col 1
 

·
Registered
Joined
·
2,702 Posts
Hi Ian. Your company loves to drop you in it, don't they? :(

The "time zones" stuff is way difficult to set up, not to say lengthy. I'll maybe get back to you.

For the "flag weekends" thing, something like

=IF(WEEKDAY(A2,2)<6,"Regular","Weekend")

tho' you have to watch the Type (",2") argument, it can be temperamental.

Rgds,
Andy
 

·
Registered
Joined
·
2,702 Posts
This is not a question, it's a project. Here are a few tips.

1. You can't use simple D2-C2 (finish time minus start time) where times span midnight. Instead you would need

=(D2-C2)+(C2<D2)

2. I'd recommend defining a named formula for the 45 min break to keep it clean. To do this, CTRL+F3, then enter "Break" (without quotes) for Names in Workbook and

=(1/24)*0.75

for Refers To, then click OK.

Then your overall formula for calcing net hours would be

=((D2-C2)+(C2<D2))-Break

>> i need to set a vlookup table

Several, probably ; let's start with the first one. Use 2 free columns, e.g. J & K. J1 = "Name", K1 = "Agency".

You should set up a dynamic named range so that you don't have to keep updating references in formulas using the table. Something like (CTRL+F3) -

(Names in Workbook)
"Agencies"

Refers To
=OFFSET(Sheet1!$J$1,0,0,COUNTA(Sheet1!$J:$J),2)

Then to VLOOKUP an agency you'd use
=VLOOKUP(B2,Agencies,2,FALSE)

, where B2 contains the first name-entry.

Rgds,
Andy
 

·
Registered
Joined
·
2,702 Posts
Re-read your post, (think) I have a slightly clearer picture & possibly an idea.

Each agency would have a "rates" table 3 columns wide.

The first 2 columns would be Monday - 00:00 ; Monday - 01:00, etc., down to Sunday - 23:00 in row 168. Then you'd have to carry on with Monday - 00:00 ; Monday - 01:00, down to Monday - 23:00 in row 192, to cover for anyone working Sunday night - Monday morning.

In the 3rd column, you'd have the hourly rate for whatever hour (of whatever day) was to the left.

From the person's start time/day and end time/day, you'd identify the start and end "span cells" in column 3, and sum the range bound by (& including) them.

The only snag I can immediately see is a twist to cover e.g. starting at half-past and finishing at quarter-to.

NB - basically it's some kind of sliding-scale ready-reckoner, tho' I still feel it would take several straight hours to set up & perfect.

HTH,
Andy
 

·
Registered
Joined
·
2,702 Posts
I've done a little work on this. As before, I have a 192-row * 3-column table. I have 3 input cells -- Date, Start Time, End Time.

I've defined rate ranges as 0600- 1759 = 5 (£s, $s, whatever), 1800 - 0559 = 6 (ignoring special e.g. weekend rates for the time being).

With Monday = 0 through Sunday = 6, a formula offsets from the top of table by x cells + y cells (x = weekday of date entered *24, y = # of hours of start time from 0000), this defines the first cell to sum in column C.

Another formula offsets from the top of table by x cells + y cells (x = weekday of date entered *24, y = # of hours of end time from 0000), this defines the last cell to sum in column C.

Effectively this creates a "virtual sum range" in column C from Date, Start Time, End Time.

Test 1 - 19th Feb from 0800 to 1600 returns 40 (8 hours * 5)
Test 2 - 19th Feb from 1200 to 2200 returns 54 (6*5 + 4*6)

(Mental note - offsetting would need to cater for end time being on a different day than start time).

Before I venture any further, I'll check you know all possible parameters.

For example, Someone starts at 14:00 & finishes at 22:00. The night rate for their agency kicks in at 18:00. What proportion of their 45-minute break is deducted at (a) day rate (b) night rate?

Rgds,
Andy
 

·
Registered
Joined
·
84 Posts
Discussion Starter · #6 ·
thanks for that Andy, i was wondering why i was getting no mail o0n my other PC, then i have just come to my laptop and found the mail open with all the received mail.
OK first non of the 45 min break is deducted at a rate, it is taken off at the end, so if some one starts at 14:00 his finish time would be 22:45, then - his 45 min break giving a total of 8 hours worked, 4 hour`s before 18:00 and 4 hours after, so he would get paid at 4 hours day rate and 4 hours at night rate
also from what i have seen that u have suggested, sometimes the start times are on the half hour and not the hour, so would you have to do it in half hour slots instead of 1 hour slots.

Thanks again for that Andy, I will set to work now and see what i can do with the information that you have given me

Ian
 

·
Registered
Joined
·
2,702 Posts
>> so would you have to do it in half hour slots
>> instead of 1 hour slots

That this statement may be incorrect is besides the point. The point beginning that there are perhaps many parameters of which this is just one (possibly). Which is why this is a project, not just a question.

I'm happy to help you over the next few days, however it's inappropriate (IMO) to clog up the board with one question, then another problem crops up so another question, etc.

If you want to collaborate, mail me at

[email protected]<no-spam>ntlworld.com

Best rgds,
Andy
 
1 - 7 of 7 Posts
Status
Not open for further replies.
Top