Add 5 mins depending on hours worked

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
you mean something like this?

srcsrcAddition
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​
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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