Excel data/time help please

Soldato
Joined
18 Oct 2002
Posts
7,515
Location
Maidenhead
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
 
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")
 
Ive fixed the issue above.

So my next question lol

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
 
Back
Top Bottom