Overtime calc on Timesheet

[ricjen]

From previous very helpful posts (thanks again guys) I have been using very successfully for almost 1 year the

" =(C2 >D2)*MEDIAN(0,D2-1/4,1/2)+MAX(0,MIN(3/4,D2+(C2 >D2))-MAX(1/4,C2)) "

approach to sort out Day/Night Hours. Its bomb proof!

A new situation demands overtime payments......start and finish time can be any time day or night (crap job!), overtime is payable after 8 hours. Thus I have day (0600-1800) standard rate, day (0600-1800) overtime rate, night (1800-0600) standard rate, night (1800-0600) overtime rate.

So, starting at 1400 and finishing at 0100 give 4 hours day std + 4 hours std night + 3 hours night o/time; whereas starting at 0200 and finishing at 1300 gives 4 hours std night + 4 hours day std + 3 hours day o/time.

Any ideas how I can automate this please?? I'm using Excel 2003 and 2007 so use the Excel 97-2003 format. Thanks in anticipation of your help.

Richard J

 

[ricjen]

the overall logic is totally beyond me...I'm just grateful for barry houdini!

 

[Kirnon]

the overall logic is totally beyond me...I'm just grateful for barry houdini!

I think I can work out most of it - what is stumping me is the formula for day - what does the very first bit do. if you break it up it brings back the statement TRUE but how does that calculate with the MEDIAN formula?

I am hoping that Barry Houdini will return with an answer.

 

[barry houdini]

Hello Kirnon,

The original formula I posted for ricjen was one I'd worked on previously, I modified it here for the specific circumstances.

In this part

=(C2>D2)*MEDIAN(0,D2-K$2,K$3-K$2)

the (C2>D2) returns TRUE or FALSE as you say. When you multiply TRUE by something it becomes 1 (FALSE is zero) so it's the equivalent of this IF function

=IF(C2>D2,MEDIAN(0,D2-K$2,K$3-K$2),0)

I just used the former to save a few characters....

If you want anything else explained let me know. I could do the whole thing but it might take some time

 

[Kirnon]

Aaahh..... I get it - I thought it might be an IF replacement but I was just guessing.

I will try and work it out by myself because that way I can ingrain it better - btw the formula is exactly what I need too. I am just trying to get my FormBox to increment the cell values of that and I will be done.

 

[rbrhodes]

Amazing effort Barry,

Anybody got an answer for 2 shifts? eg

Start1 | End1 | Start2 | End 2 | Std day | Std night | OT day | OT night |


believe me I tried...


dr

 

[gilbertob]

Welcome to the forum,

Amazing effort Barry, ricjen, Kirnon and rbrhodes

Anybody got an answer for 2 shifts? eg

Start1 | End1 | Start2 | End 2 | Std day | Std night | OT day | OT night |

Gilberto - Brasil

 

[gilbertob]

Welcome to the forum

I need to adappt the formulas from Barry Houdini to work with two shifts, with a rest interval, being the rest during the day or during the night.
Overtime is payable after 8 hours.

Gilberto - Brasil

 

[gilbertob]

anyone have a solution?

 

[itries2help]

Welcome to the forum,

Amazing effort Barry, ricjen, Kirnon and rbrhodes

Anybody got an answer for 2 shifts? eg

Start1 | End1 | Start2 | End 2 | Std day | Std night | OT day | OT night |

Gilberto - Brasil

I doubt if this is being viewed by any of the above, as the post is so old. Why not post a new question? I'm new too so it's just a suggestion.

via Google Translate: Duvido que isso está sendo visto por qualquer um dos acima, como o post é tão velho. Por que não postar uma nova pergunta? Eu sou novo demais, por isso é apenas uma sugestão.

 

[aaybee]

how do i Post a new thread regarding "Timesheet"