Hi. I need to calculate the numer of hours (with a 30mim range) between diferent shifts.
For example...
I go to work at 6am and leave at 23h. As my payments are different depending of my shift i need to know how many hours i worked between 7 to 22 and how many i worked from 22 to 7.

Can you help me please? I have been thinking of a way of do this but im not getting it...

Diogo

Hi Diogo,

Am Tue, 28 Jan 2014 18:27:35 -0800 (PST) schrieb
:

For example...
I go to work at 6am and leave at 23h. As my payments are different depending of my shift i need to know how many hours i worked between 7 to 22 and how many i worked from 22 to 7.

start time in A2, end time in B2

Then for hours between 7:00 and 22:00 in C2:
=IF(B2A2,MIN(B2,TIME(22,,))-MAX(A2,TIME(7,,)),MAX(TIME(22,,)-A2,0)+MAX(B2-TIME(7,,),0))

and for hours between 22:00 and 7:00 in D2:
=MOD(B2-A2,1)-C2


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi again,

Am Wed, 29 Jan 2014 09:43:05 +0100 schrieb Claus Busch:

Then for hours between 7:00 and 22:00 in C2:
=IF(B2A2,MIN(B2,TIME(22,,))-MAX(A2,TIME(7,,)),MAX(TIME(22,,)-A2,0)+MAX(B2-TIME(7,,),0))

change the formula above to:
=IF(B2A2,MAX(MIN(B2,TIME(22,,))-MAX(A2,TIME(7,,)),0),MAX(TIME(22,,)-A2,0)+MAX(B2-TIME(7,,),0))


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks.
I will try to understand it ;)
And i will check if it works well...

Greetings
Diogo

On Wednesday, January 29, 2014 8:54:57 AM UTC, Claus Busch wrote:
Hi again,



Am Wed, 29 Jan 2014 09:43:05 +0100 schrieb Claus Busch:



Then for hours between 7:00 and 22:00 in C2:

=IF(B2A2,MIN(B2,TIME(22,,))-MAX(A2,TIME(7,,)),MAX(TIME(22,,)-A2,0)+MAX(B2-TIME(7,,),0))



change the formula above to:

=IF(B2A2,MAX(MIN(B2,TIME(22,,))-MAX(A2,TIME(7,,)),0),MAX(TIME(22,,)-A2,0)+MAX(B2-TIME(7,,),0))





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Claus, When i try it,i get an error...

http://postimg.org/image/4li0iqk95/

I don't understand what the problem...

I even tried to change A2 and B2 to date and to time, but it didnt work either...

Well, i guess i solved it... changed all , to ;

However, now the results are worng...

For example, with IN = 3:00 and OUT = 14:00 the results on C2 are 0.291667 and i would expect it to be 7 (14-7)

Hi,

Am Wed, 29 Jan 2014 05:38:52 -0800 (PST) schrieb
:

Claus, When i try it,i get an error...

http://postimg.org/image/4li0iqk95/

I made the formulas for real times so as 2:00 or 14:00
Please have a look he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for the workbook "Time"


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi,

Am Wed, 29 Jan 2014 05:44:31 -0800 (PST) schrieb
:

For example, with IN = 3:00 and OUT = 14:00 the results on C2 are 0.291667 and i would expect it to be 7 (14-7)

the result is correct. You have to format the cell hh:mm or h:mm


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks!
Did it. Works

Really Thanks

Claus, can you please explain the thinking behind the formula? Maybe to divide the formula in small pieces and explain?im asking because i would like to understand it and because i will need to do another formula for holidays with different shifts prices (7h-13h-22h) and i would like to try myself.
Thanks
Diogo

Hi,

Am Wed, 29 Jan 2014 12:30:00 -0800 (PST) schrieb
:

Claus, can you please explain the thinking behind the formula? Maybe to divide the formula in small pieces and explain?im asking because i would like to understand it and because i will need to do another formula for holidays with different shifts prices (7h-13h-22h) and i would like to try myself.
=IF(B2A2
if you work without midnightrollover the end time is greater than the
start time and the first part of the formula is calculated
MIN(B2,TIME(22,,))
your dayshift goes up to 22:00. If you stop working before it calculates
the minimum of real end time and 22:00. If you stop working at 20:00
then 20:00 is the minimum. If you are working till 0:00 then 22:00 is
minimum.
MAX(A2,TIME(7,,)),0)
Your dayshift starts at 7:00. If you are starting later this calculates
the maximum of real start time and 7:00. If you are starting at 9:00
then 9:00 is maximum. If you are starting at 5:00 then 7:00 is maximum
MAX(MIN(B2,TIME(22,,))-MAX(A2,TIME(7,,)),0),MAX(TIME(22,,)-A2,0)
If you are working from 1:00 to 3:00 B2A2 but if you calculate it with
the formula you get a negative time shown as #######
So the max(Formula,0) displays 0 cause 0 is greater than negative time
MAX(TIME(22,,)-A2,0)+MAX(B2-TIME(7,,),0))
With nightshift it is opposite to dayshift. You have to substract the
start time from 22:00 and 7:00 from end time.

Select a cell with the formula and start the formula editor and look how
it changes while calculating.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks so much
I will read it real closely so i can fully understand.
And ineill be posting here my formula for the weekends (7-13-22). Do you think its going to be similar or will i need to work it from scratch?

Done it!

Now, and at last, making a new formula for the days I'm on shifts "on call" instead of "on physical presence". Let's see how it goes...