Timesheet number of hours

Hey,


Apologies in advance if this has already been asked - but here's the deal.
I'm using Google docs to make a handy time-sheet for myself. I've figured out how to make it sum up the amount of hours between start and stop times, but the following issue has proven more complex. I've tried googling quite a bit, but either I'm bad at it, or the solution is more elusive than I had imagined. So:

Say I've worked from 14:00 to 23:00. That is 9 hours of work - easy.
Now, from 17:00 to 22:00, I get an evening bonus to my wages. From 22:00 to 01:00 there's the night bonus. (there's also a late night bonus for 01:00 to 06:00).

What I was hoping you guys could help me with, is make a formula that registers whether or not I've worked any hours that has bonuses, and if so, how many. (Working 14-23, I get 5 hours evening bonus (17-22) and 1 hour night bonus (22-01).

A1	B1	C1	D1	E1
Start	Stop	Evening	Night	Late night
14:00	23:00	9	5	1

I'm using the following formula to calculate the number of hours:
=If($A1>0,Hour($B1-$A1+($B1<$A1))+Minute($B1-$A1+($B1<$A1))/60,"")

Cross-Post:
http://www.excelguru.ca/forums/showt...umber-of-hours

Not exactly the answer I was looking for..

While normally I wouldn't condone cross-posting (don't want to reinvent the wheel, or waste folks' time on other forums), but this one was straight-forward enough that I'll let it slide.

See attached.

Thank you for your valuable time and helpful answer, much obliged.

Now, as for cross-posting.
Cross-posting in different sub-sections of a forum, that I can see. "Cross-posting" between two Different forums? I don't get it.

Different people use different forums at different times - so if you post a question several different places, your chances of a reply/solution increases.

Should the same person (such as rollis13) happen to frequent the same two forums and notice an identical question having been posted two different places, he's free to ignore them. As for wasting his time? I can't say that I care - I'm not forcing him to read them. Although, obviously he did, since he took the time to inform the rest of the world of my heinous crime, instead of providing the answer - which, according to Yogi52o, was so very simple.

I guess everything is easy once you know how to do it..

Cross-posting in same Forum is Duplicated posting.
It's not allowed.

Cross-posting between different forum is allowed (as you said, increase chance to get your answer quickly) only you need to put link to cross-posted question becasue if it's already answered somewhere else then it's not fair to waste our time to try to solve your (already answered) question.

rollis13 posted a link to your other post - in case if it's solved we can skip it.

Internet people who actually care about other internet people? I am mildly shocked to be honest.

I will post on the Guru forum immediately, informing them of having received an answer here!

That would be nice.

So, next problem, which I've been working on for a while now.

My work has different bonuses for evenings, nights and late nights, as previously stated.
However, there is also a separate bonus for weekends, starting 15:00 on Saturday and ending on 24:00 Sunday. This bonus is bigger than the evening and night bonus, but smaller than the late night bonus. Only one bonus will apply at a given hour, the biggest one, obviously.

I need a formula that will show how many hours of weekend bonus I get, but it also needs to consider whether or not there is a late night bonus that needs to be applied. Added my current worksheet, should anyone of you have the time to help.

So:

A1	B1	C1	D1	E1	F1	G1	H1	I1	J1
Day	Sa	Su	Start	Stop	Hours	Evening	Late	Night	Weekend
Saturday	x		14:00	23:00	9	0	0	0	8
Sunday		x	05:00	15:00	10	0	0	1	9

Any help would be appreciated!

So, anyone?

It's not that easy and it would be best to use UDF.
Are you OK with that?

Heh yeah, I've discovered it's not that easy I tried making some rather long and nasty If sentences, but have yet to make it work properly.

I have no experience with UDF (user-defined function I take it?), but if I could get some assistance with setting it up, I sure wouldn't mind using it.

I found an old Norwegian Excel doc that uses such a function, but it was outdated and incompatible with Google Docs.