work hours with different hour prices

[diogoribeiromachado85]

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

 

[Claus Busch]

Hi Diogo,

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

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(B2>A2,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.

 

[Claus Busch]

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(B2>A2,MIN(B2,TIME(22,,))-MAX(A2,TIME(7,,)),MAX(TIME(22,,)-A2,0)+MAX(B2-TIME(7,,),0))

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


Regards
Claus B.

 

[diogoribeiromachado85]

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

Greetings
Diogo

 

[diogoribeiromachado85]

Hi again,



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







change the formula above to:

=IF(B2>A2,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...

 

[diogoribeiromachado85]

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)

 

[Claus Busch]

Hi,

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

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 here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Time"


Regards
Claus B.

 

[Claus Busch]

Hi,

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

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.

 

[diogoribeiromachado85]

Thanks!
Did it. Works

Really Thanks

 

[diogoribeiromachado85]

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 holidayswith different shifts prices (7h-13h-22h) and i would like to try myself.
Thanks
Diogo

 

[Claus Busch]

Hi,

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

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(B2>A2
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 B2>A2 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.

 

[diogoribeiromachado85]

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?

 

[diogoribeiromachado85]

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...