# Add 5 mins depending on hours worked

#### Siyanna

##### Well-known Member
Hi All

Im trying look at a start and end time and trying give 5 mins for every completed hour worked

ie if someone worked 02:55 hours then that should return 10 mins as there are only 2 completed full hours but struggling with this formula

Also trying to get a multiple or sumifs to total up every time that isnt in my condition list

hoping you can help me please

many thanks

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
you mean something like this?

 src src Addition 02:55​ 0.02:55:00​ 0.03:05:00​ 04:07​ 0.04:07:00​ 0.04:27:00​ 05:11​ 0.05:11:00​ 0.05:36:00​ 01:01​ 0.01:01:00​ 0.01:06:00​ 00:45​ 0.00:45:00​ 0.00:45:00​

Hi

Not quite

say a start time was 08:20 and End Time was 09:30
i wang the cell to display 00:05:00 as there is only 1 full hour during that period

if say it was 08:30 start and 10:55 then it should say 00:10:00 as there are 2 full hours in that period

so give 5 mins for evert full hour done

but if duration is 33:25 (hh:mm) what you want to see?

With your Start Time in cell A1 and End Time in cell B1, try

=FLOOR(B1-A1,"1:00")/12

Custom-format the result as hh:mm:ss

A
B
C
D
E
F
20
Worked HoursMinutes1Minutes2Minutes
21
00:05​
00:00​
00:00​
00:00:00​
22
01:05​
05:00​
00:05​
00:50:00​
23
02:25​
10:00​
00:10​
00:10:00​
24
03:45​
15:00​
00:15​
00:15:00​
25
10:33​
50:00​
00:50​
00:50:00​
26
23:59​
115:00​
01:55​
00:115:00​
27
12:45​
60:00​
01:00​
00:60:00​
28
13:55​
65:00​
01:05​
00:65:00​
29
16:30​
80:00​
01:20​
00:80:00​
30
05:47​
25:00​
00:25​
00:25:00​
31
18:00​
90:00​
01:30​
00:90:00​
32
04:40​
20:00​
00:20​
00:20:00​
33
27:50​
15:00​
00:15​
00:15:00​
34
35
[hh]:mm[mm]:ss][hh]:mm

C21 / D21: =HOUR(A21)*5/1440 with different custom format
Green table: PowerQuery
Max worked hours = 23 , if greater [row 33] you'll see (27-24)*5 =>> 15

Last edited:
Correction:

A
B
C
D
E
F
20
Worked HoursMinutes1Minutes2Minutes
21
00:05​
00:00​
00:00​
00:00:00​
22
01:05​
05:00​
00:05​
00:05:00​
23
02:25​
10:00​
00:10​
00:10:00​
24
03:45​
15:00​
00:15​
00:15:00​
25
10:33​
50:00​
00:50​
00:50:00​
26
23:59​
115:00​
01:55​
00:115:00​
27
12:45​
60:00​
01:00​
00:60:00​
28
13:55​
65:00​
01:05​
00:65:00​
29
16:30​
80:00​
01:20​
00:80:00​
30
05:47​
25:00​
00:25​
00:25:00​
31
18:00​
90:00​
01:30​
00:90:00​
32
04:40​
20:00​
00:20​
00:20:00​
33
27:50​
15:00​
00:15​
00:15:00​
34
35
[hh]:mm[mm]:ss][hh]:mm

Thank You All

You are welcome

Replies
1
Views
150
Replies
1
Views
208
Replies
1
Views
125
Replies
1
Views
320
Replies
2
Views
169

1,203,472
Messages
6,055,610
Members
444,803
Latest member
retrorocket129

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back