Add 5 mins depending on hours worked

S

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 calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
you mean something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]src[/td][td][/td][td=bgcolor:#70AD47]src[/td][td=bgcolor:#70AD47]Addition[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
02:55​
[/td][td][/td][td=bgcolor:#E2EFDA]
0.02:55:00​
[/td][td=bgcolor:#E2EFDA]
0.03:05:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
04:07​
[/td][td][/td][td]
0.04:07:00​
[/td][td]
0.04:27:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
05:11​
[/td][td][/td][td=bgcolor:#E2EFDA]
0.05:11:00​
[/td][td=bgcolor:#E2EFDA]
0.05:36:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
01:01​
[/td][td][/td][td]
0.01:01:00​
[/td][td]
0.01:06:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
00:45​
[/td][td][/td][td=bgcolor:#E2EFDA]
0.00:45:00​
[/td][td=bgcolor:#E2EFDA]
0.00:45:00​
[/td][/tr]
[/table]
 
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
[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
20​
[/td][td=bgcolor:#5B9BD5]Worked Hours[/td][td][/td][td=bgcolor:#5B9BD5]Minutes1[/td][td=bgcolor:#5B9BD5]Minutes2[/td][td][/td][td=bgcolor:#70AD47]Minutes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
21​
[/td][td=bgcolor:#DDEBF7]
00:05​
[/td][td][/td][td=bgcolor:#DDEBF7]
00:00​
[/td][td=bgcolor:#DDEBF7]
00:00​
[/td][td][/td][td=bgcolor:#E2EFDA]
00:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
22​
[/td][td]
01:05​
[/td][td][/td][td]
05:00​
[/td][td]
00:05​
[/td][td][/td][td]
00:50:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
23​
[/td][td=bgcolor:#DDEBF7]
02:25​
[/td][td][/td][td=bgcolor:#DDEBF7]
10:00​
[/td][td=bgcolor:#DDEBF7]
00:10​
[/td][td][/td][td=bgcolor:#E2EFDA]
00:10:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
24​
[/td][td]
03:45​
[/td][td][/td][td]
15:00​
[/td][td]
00:15​
[/td][td][/td][td]
00:15:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
25​
[/td][td=bgcolor:#DDEBF7]
10:33​
[/td][td][/td][td=bgcolor:#DDEBF7]
50:00​
[/td][td=bgcolor:#DDEBF7]
00:50​
[/td][td][/td][td=bgcolor:#E2EFDA]
00:50:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
26​
[/td][td]
23:59​
[/td][td][/td][td]
115:00​
[/td][td]
01:55​
[/td][td][/td][td]
00:115:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
27​
[/td][td=bgcolor:#DDEBF7]
12:45​
[/td][td][/td][td=bgcolor:#DDEBF7]
60:00​
[/td][td=bgcolor:#DDEBF7]
01:00​
[/td][td][/td][td=bgcolor:#E2EFDA]
00:60:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
28​
[/td][td]
13:55​
[/td][td][/td][td]
65:00​
[/td][td]
01:05​
[/td][td][/td][td]
00:65:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
29​
[/td][td=bgcolor:#DDEBF7]
16:30​
[/td][td][/td][td=bgcolor:#DDEBF7]
80:00​
[/td][td=bgcolor:#DDEBF7]
01:20​
[/td][td][/td][td=bgcolor:#E2EFDA]
00:80:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
30​
[/td][td]
05:47​
[/td][td][/td][td]
25:00​
[/td][td]
00:25​
[/td][td][/td][td]
00:25:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
31​
[/td][td=bgcolor:#DDEBF7]
18:00​
[/td][td][/td][td=bgcolor:#DDEBF7]
90:00​
[/td][td=bgcolor:#DDEBF7]
01:30​
[/td][td][/td][td=bgcolor:#E2EFDA]
00:90:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
32​
[/td][td]
04:40​
[/td][td][/td][td]
20:00​
[/td][td]
00:20​
[/td][td][/td][td]
00:20:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
33​
[/td][td=bgcolor:#FFFF00]
27:50​
[/td][td][/td][td=bgcolor:#FFFF00]
15:00​
[/td][td=bgcolor:#FFFF00]
00:15​
[/td][td][/td][td=bgcolor:#FFFF00]
00:15:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
34​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
35​
[/td][td][hh]:mm[/td][td][/td][td][mm]:ss][/td][td][hh]:mm[/td][td][/td][td][/td][/tr]
[/table]

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:

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
20​
[/td][td=bgcolor:#5B9BD5]Worked Hours[/td][td][/td][td=bgcolor:#5B9BD5]Minutes1[/td][td=bgcolor:#5B9BD5]Minutes2[/td][td][/td][td=bgcolor:#70AD47]Minutes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
21​
[/td][td=bgcolor:#DDEBF7]
00:05​
[/td][td][/td][td=bgcolor:#DDEBF7]
00:00​
[/td][td=bgcolor:#DDEBF7]
00:00​
[/td][td][/td][td=bgcolor:#E2EFDA]
00:00:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
22​
[/td][td]
01:05​
[/td][td][/td][td]
05:00​
[/td][td]
00:05​
[/td][td][/td][td]
00:05:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
23​
[/td][td=bgcolor:#DDEBF7]
02:25​
[/td][td][/td][td=bgcolor:#DDEBF7]
10:00​
[/td][td=bgcolor:#DDEBF7]
00:10​
[/td][td][/td][td=bgcolor:#E2EFDA]
00:10:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
24​
[/td][td]
03:45​
[/td][td][/td][td]
15:00​
[/td][td]
00:15​
[/td][td][/td][td]
00:15:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
25​
[/td][td=bgcolor:#DDEBF7]
10:33​
[/td][td][/td][td=bgcolor:#DDEBF7]
50:00​
[/td][td=bgcolor:#DDEBF7]
00:50​
[/td][td][/td][td=bgcolor:#E2EFDA]
00:50:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
26​
[/td][td]
23:59​
[/td][td][/td][td]
115:00​
[/td][td]
01:55​
[/td][td][/td][td]
00:115:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
27​
[/td][td=bgcolor:#DDEBF7]
12:45​
[/td][td][/td][td=bgcolor:#DDEBF7]
60:00​
[/td][td=bgcolor:#DDEBF7]
01:00​
[/td][td][/td][td=bgcolor:#E2EFDA]
00:60:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
28​
[/td][td]
13:55​
[/td][td][/td][td]
65:00​
[/td][td]
01:05​
[/td][td][/td][td]
00:65:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
29​
[/td][td=bgcolor:#DDEBF7]
16:30​
[/td][td][/td][td=bgcolor:#DDEBF7]
80:00​
[/td][td=bgcolor:#DDEBF7]
01:20​
[/td][td][/td][td=bgcolor:#E2EFDA]
00:80:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
30​
[/td][td]
05:47​
[/td][td][/td][td]
25:00​
[/td][td]
00:25​
[/td][td][/td][td]
00:25:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
31​
[/td][td=bgcolor:#DDEBF7]
18:00​
[/td][td][/td][td=bgcolor:#DDEBF7]
90:00​
[/td][td=bgcolor:#DDEBF7]
01:30​
[/td][td][/td][td=bgcolor:#E2EFDA]
00:90:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
32​
[/td][td]
04:40​
[/td][td][/td][td]
20:00​
[/td][td]
00:20​
[/td][td][/td][td]
00:20:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
33​
[/td][td=bgcolor:#FFFF00]
27:50​
[/td][td][/td][td=bgcolor:#FFFF00]
15:00​
[/td][td=bgcolor:#FFFF00]
00:15​
[/td][td][/td][td=bgcolor:#FFFF00]
00:15:00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
34​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]
35​
[/td][td][hh]:mm[/td][td][/td][td][mm]:ss][/td][td][hh]:mm[/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Working Hours Taken to Complete a Task
Replies
6
Views
321
AhoyNC
A
B
  • Solved
Issue with finding time difference taking account of weekends and workdays.
2
Replies
15
Views
463
Joe4
Joe4
T
  • Question Question
enter time without colon
Replies
5
Views
202
etaf
etaf
R
  • Question Question
Time sheet formulas
Replies
2
Views
374
rodnaaaaay
R
R
  • Solved
Adding Hours & Minutes to Dates & Times w/in Working Hours
2
Replies
11
Views
670
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

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