Need help making a function in Excel 2007?

Question

Hi everybody. I'm trying to make a little excel sheet to where you put time (in 24 hour format), and then it tells you how many hours go into a particular payment tier.
the 4 tiers are:
tier 1. mon-fri, 6am-6pm
tier 2. mon-fri, 6pm-6am & sat 6am-6pm
tier 3. sat 6pm-6am
tier 4. All sun
so lets say you work from 5am-5pm on a monday, i'd need 11 hours in tier 1 box, and 1 hour in tier 2 box. and so on and so forth. Right now i have it to where you input the date, and it'll automatically calculate the next 9 days. then i'd put in my starting time in one box, and my ending time in the next box for each date. I just can't figure out how to function it to count certain hours for one tier and the other hours for the other tiers. Thank you in advance.

(Bonus Question). If this is to easy for you, then how can you add holidays to tier 4.

puaka · Accepted Answer

Create a table 

Day 1 to Day 7 (Sun as day 7) in column 1, a2 to a8.

On Cell B1, enter 0:00, 1:00 and fill right until 24:00

Within the table, enter all the relevent 4 tiers applicable to the day of week and time of day.

Using vlookup(), match() functions creative you can find out what tier that particular hours of work it belongs too. Next you apply all the business rules including min hours, max overtime or what have you.

Anyway such convoluted schemes are very hard to work out so by the time you finished you can call yourself an Excel expert.

Need help making a function in Excel 2007?

Related Questions

Trending Questions