Date time issues

[A[L]C]

hi all,

i have the following sheet:

http://paulharman.fmguy.com/Overtime Claim Form ocuk.xlsm

I have an issue working with times.

In cell H5, I have it working so if A5 is on call, it works it out in 4 hours blocks, or if it is working time, it works out how many hours.

I also have the multipler in I5, 2x on bank holidays or sundays, 1.5 the rest of the time, with a minimum of 3 hours.

How do I work out the total time? ie 6 hours overtime at 1.5x should give 9 hours total.

Thanks

 

[A[L]C]

ok this seems to work

=IF(H5="","",((HOUR(H5)*60+MINUTE(H5))/60)*I5)

So, next question... how do I change H5, to round up to the nearest hour when over 3 hours (if A5 is "Working")

 

[A[L]C]

http://paulharman.fmguy.com/Overtime Claim Form 2010 ocuk.xlsm

Thats where I am at so far.

However if I put in such dates as

Start:

01/05/2010 09:00

Finish

02/05/2010 16:00

It gives me 7 hours. It should be 31 hours. How can I change this?

 

[VoG]

Like this?

Excel Workbook
	A	B	C
1	01/05/2010 09:00	02/05/2010 16:00	31
Sheet2

 

[A[L]C]

Thanks, managed to change my formula so now it shows the hours correctly when its over 23 hours.

But

I need to change column H to match the new non time format...

=IF(OR(C5="",D5="",E5="",F5="")=TRUE,"",(IF(G5="Date Error","",(IF(A5="Working",IF(G5<0.125,0.125,(TIME((ROUNDUP((HOUR(G5)*60+MINUTE(G5))/60,0)),"0","0"))),IF(A5="On Call",ROUNDUP(((HOUR(G5)*60+MINUTE(G5))/60)/4,0)&" On-Call block(s)",""))))))

This is my current formula. Is there a better way to do it?

It first checks if C5, D5, E5 or F5 are blank, if they are it returns blank.
It then checks if G5 is Date Error and if it is returns blank
Then if A5 is "Working", we need to round H5 up to the nearest hour with a minimum of 3 hours
If A5 is "On Call" then we break it down to 4 hours chunks and round up

 

[A[L]C]

Think Ive got it

=IF(OR(C5="",D5="",E5="",F5="")=TRUE,"",(IF(G5="Date Error","",(IF(A5="Working",IF(G5<3,3,ROUNDUP(G5,0)),IF(A5="On Call",ROUNDUP(G5/4,0)&" On-Call block(s)",""))))))

 

[A[L]C]

Thanks for your help VoG. Next question is...

Is there a better way I can get users to input the time to ensure it goes in the correct format? ie like the calendar control but for time?

if not I guess its a custom userform.... I could break it in to two boxes one for hour, one for minutes. How would I get the cursor to auto jump to the minute box after the hour was filled in.... ie start typing 09 then it jumps to the minutes so I can enter 30. For 09:30?

Thanks

 

[VoG]

You can use a Date/Time picker http://www.familycomputerclub.com/excel/macros_date_time_picker_control.swf.html