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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,799
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
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,579

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

Well-known Member
Joined
Oct 24, 2015
Messages
6,799
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

Well-known Member
Joined
Oct 24, 2015
Messages
6,799

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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,436
Messages
5,528,758
Members
409,834
Latest member
vexceled

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top