Need to calculate night shift hours

**avdald** · 08-21-2012, 03:52 PM

Firstly thanx to all who try to help.

My problem is thus:

We get paid at a different rate for night work than daytime work. Night hours are 23:00 to 7:00. However, sometimes I start early and finish late, but only the time between those hours get the higher rate. Is there a formula to calculate this?

Eg 1- I work from 22:57 until 7:08 - my night hours are 8 (the max)
Eg 2- I work from 23:05 until 7:00 - my night hours are 7:55
Eg 3- I work from 23:00 until 6:55 - my work hours are 7:55 etc

(Btw, I need it for google docs, plus going past midnight I solved already

)

This is part one. Part two is overtime depending on whether it is during the day or night. But I'll post that once the first problem is sloved

**nath_rk01** · 08-23-2012, 04:45 AM

Hi, Please find the attached files to get your answer... i hope this will solve your problem...

**Pepe Le Mokko** · 08-23-2012, 04:47 AM

Perhaps use a Google docs forum. XL solutions might not be applicable

**avdald** · 08-23-2012, 05:23 AM

Originally Posted by nath_rk01

Hi, Please find the attached files to get your answer... i hope this will solve your problem...

It looks great. But it only works if there is some night shift involved. What I really need is a way that the result will be blank if the entry and exit are both during the daytime. Also I have an entry at 20:50 and exit at 23:35 which is giving me a result of 2:35 (same as total hours) and not 0:35 which is the correct amount???

Thanx again for your help. At lest now I have a direction on the problem.

**daddylonglegs** · 08-23-2012, 05:24 AM

Try this formula

=MOD(C3-B3,1)-(C3<B3)*2/3-MEDIAN(MOD(C3,1),"7:00"+0,"23:00"+0)+MEDIAN(MOD(B3,1),"7:00"+0,"23:00"+0)

format result cell in time format

This formula should work OK in both excel and google docs

**avdald** · 08-23-2012, 05:25 AM

Originally Posted by Pepe Le Mokko

Perhaps use a Google docs forum. XL solutions might not be applicable

I'm a noob here - sorry. You can move it if you want or tell me how to please.

**nath_rk01** · 08-23-2012, 06:56 AM

Good One... thanks daddylonglegs...

@ avdald... you should try this one.. will give you the result exactly what you want...

**avdald** · 08-23-2012, 09:20 AM

daddylonglegs - looking at other posts for a possible solution I had a feeling you would have the answer!

When last were you told that you're a genius? Let me make that right now! YOU"RE A GENIUS

Thanx a million.

**Cutter** · 08-23-2012, 12:39 PM

@ avdald

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**avdald** · 08-23-2012, 02:25 PM

I'm sorry - I'm still a noob here. But hopefully won't be for long. I still don't see how to mark a thread as solved. Btw, I was going to do some more testing before deciding that it really was solved.

I have starred and approved the 2 guys who helped me. They both deserve it!!!