Overtime calculation

[alamgiritbd]

Hi,


I want to calculate over time (working Hour) in excel 2007. My cell format is h:mm. and time format is 24h. I have 4 column for this:

1. Departure Time (D14)

2. Arrival Time (F14)

3. Travel Time (G14)

4. Over Time (K14)


Standard working hour is 10 Hour. i need to calculate an worker how many hour/s worked after 10h.


For G14 i use

=IF(F14*M14>0,(IF(D14*M14>0,((IF(F14>=D14,(F14-D14),(F14-D14+1)))),"0")),"0")

And its show perfect value what I want.


And for K14 I use

=IF(B14="0",B14,IF(G14<N14,G14+1,G14)-N14)

Here N14 is standard working hour. And M14 for reference for count trip or show 0.


Now the problem is when a worker work below 10h then cell K14 show wrong answer/value.


For ex:

d14(8:00)---f14(17:00)---then it's showing on g14 as(9:00) and k14(23:00). So there is g14 is right but it show wrong value in k14.

I want to show here 0:00 when it is equal or below 10h.


Thanks

 

[xld]

How about


=MAX(0,G14-N14)

 

[Hui]

I don't know what B14 does

and assuming N14 is 10

then for K14 try:

=IF(B14="0",B14,IF(G14<N14,0,G14-N14))

 

[alamgiritbd]

xld,


it is working. Thanks

but i need to chk with b14. because i need when in b14 have value then k14 will calculate the over time.


Hui,

I think you got my question.

I try your formula but it show ##### when g14 is 0.

 

[xld]

Okay, just


=IF(B14=0,0,MAX(0,G14-N14))

 

[alamgiritbd]

Thanks you both.