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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
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​
 

Siyanna

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

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
but if duration is 33:25 (hh:mm) what you want to see?
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,747

ADVERTISEMENT

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
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
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:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497

ADVERTISEMENT

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,591
Members
430,557
Latest member
MK15

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
Top