Cannot format cells into (h):mm

shina67

Board Regular
Joined
Sep 18, 2014
Messages
141
Hi All,

I have tried in vain to convert the attached to (h):mm format so that I can do some calculations.
I have tried formatting which doesn't seem to work. I have also tried various different formulas.
I am probably missing something simple.
I would also ideally llike the rows that do not contain data deleting i.e. rows 1-4, 34-40, 69-75 etc.
obviously the attached is a smaller sheet than the actual one i have to work with. This sheet I have to manipulate on a daily basis.
Its the columns E through to N that need converting to {h]:mm.

Hope this makes sense.

I am hoping one of you lovely lot can help and save me a lot of time!!!.

Timesheet - Daily Hours Totals.xlsx
ABCDEFGHIJKLMN
1
2Timesheet - Daily Hours Totals
3Tue 10 January 2023From 09 January 2023 to 09 January 2023
4
5BadgePayroll No.NameBasicT/QtrT/HalfDoubleRate05Rate06SickAbsenOther RatesTotal
6000066GEOFFREY NEIL GREENWOOD8:001:000:000:000:000:000:000:000:009:00
700040607GARY STEVEN VINE8:003:150:000:000:000:000:000:000:0011:15
80004242ROBERT MOUNTAIN8:002:300:000:000:000:000:000:000:0010:30
90009494ALADIN BOLIC5:150:000:000:000:000:000:000:000:005:15
1000113113JOHN DAVID MASSINGHAM0:000:000:000:000:000:000:000:000:000:00
1100131131MARK ANTHONY SPENCER8:004:450:000:000:000:000:000:000:0012:45
1200200200MARTIN JOHN WALKER8:000:300:000:000:000:000:000:000:008:30
1300202202ANDREW SENIOR0:000:000:000:000:000:000:000:000:000:00
1400217217GARY MADDOX8:000:000:000:000:000:000:000:000:008:00
1500250250PAUL DAY8:000:000:000:000:000:000:000:000:008:00
1600289289PAUL IAN HEWITT8:001:000:000:000:000:000:000:000:009:00
1700295295NEIL RIDDLE8:000:000:000:000:000:000:000:000:008:00
1800333333STUART ANDREW BROADBENT8:000:000:000:000:000:000:000:000:008:00
1900341341MARIA NOEMI DE FREIT JOAO8:001:000:000:000:000:000:000:000:009:00
2000344344ADRIAN PAUL CHAPMAN8:000:000:000:000:000:000:000:000:008:00
2100353353LEON BEMPA OWUSU8:003:150:000:000:000:000:000:000:0011:15
2200368368CHRISTOPHER KEAR0:000:000:000:000:000:000:000:000:000:00
2300384384STEFAN WOLOSZYN8:000:000:000:000:000:000:000:000:008:00
2400463463JOHN MARTIN FISHER8:000:000:000:000:000:000:000:000:008:00
2500471471ARRON ASHLEY THOMAS8:000:450:000:000:000:000:000:000:008:45
2600487487RICHMOND OFOSU8:002:150:000:000:000:000:000:000:0010:15
2700501501CHRISTOPHER MATTHEW GRAY8:000:000:000:000:000:000:000:000:008:00
2800512512KEITH SMITH8:000:000:000:000:000:000:000:000:008:00
2900514514TROY KIRKLAND PHILLIP8:000:000:000:000:000:000:000:000:008:00
3000525525RUSSELL SPENCER8:000:000:000:000:000:000:000:000:008:00
3100565565STEVEN IVANKIV8:000:000:000:000:000:000:000:000:008:00
3200585585ADAM FIRTH8:000:000:000:000:000:000:000:000:008:00
3300614614JASON WADE8:001:000:000:000:000:000:000:000:009:00
34
3504/00531
36
37Timesheet - Daily Hours Totals
38Tue 10 January 2023From 09 January 2023 to 09 January 2023
39
40BadgePayroll No.NameBasicT/QtrT/HalfDoubleRate05Rate06SickAbsenOther RatesTotal
4100628628JAMES NEWMAN8:000:000:000:000:000:000:000:000:008:00
4200638638MARK SCOTT8:000:000:000:000:000:000:000:000:008:00
4300641641DAVID OLIVER8:000:000:000:000:000:000:000:000:008:00
4400660660JASON MITCHELL8:000:000:000:000:000:000:000:000:008:00
4500678678LOUIE WHITE8:000:000:000:000:000:000:000:000:008:00
4600684684DEAN STANLEY8:000:000:000:000:000:000:000:000:008:00
4700688688SENAD IMEROSKI8:000:000:000:000:000:000:000:000:008:00
4800690690BRANDON ROBERTSON8:000:000:000:000:000:000:000:000:008:00
4900694694THOMAS JAKE LUNN0:000:000:000:000:000:000:000:000:000:00
5000702702SCOTT WATSON0:000:000:000:000:000:000:000:000:000:00
5100710710STEVEN COOK7:450:000:000:000:000:000:000:000:007:45
5200711711DARREN JOSEPH SANDERS8:000:000:000:000:000:000:000:000:008:00
5300716716RUI DE ABREU8:002:450:000:000:000:000:000:000:0010:45
54007180718JODIE TOLLERFIELD0:000:000:000:000:000:000:000:000:000:00
5500728728EVANS OFORI8:000:300:000:000:000:000:000:000:008:30
5600733733LIAM CHEALE8:000:000:000:000:000:000:000:000:008:00
5700755755DANIEL CRAIG WHITELEY8:000:000:000:000:000:000:000:000:008:00
5800757757LOUISE MICHELLE GRESTY8:000:000:000:000:000:000:000:000:008:00
5900760760HAYDN BOWLER0:000:000:000:000:000:000:000:000:000:00
6000767767ALLAN WALTERS7:300:000:000:000:000:000:000:000:007:30
6100770770ADAM DAVIS8:000:000:000:000:000:000:000:000:008:00
6200787787JONATHON PAUL COWLING8:001:000:000:000:000:000:000:000:009:00
6300796796JAMIE SHAW8:000:000:000:000:000:000:000:000:008:00
6400801801NEIL LORIMER8:001:000:000:000:000:000:000:000:009:00
6500817817DANIEL JENNINGS8:003:150:000:000:000:000:000:000:0011:15
6600826826KYLE EMMONDS8:001:000:000:000:000:000:000:000:009:00
6700828828JOHN MORRELL8:000:000:000:000:000:000:000:000:008:00
6800834834ALEX ALHASSAN8:000:000:000:000:000:000:000:000:008:00
69
7004/00532
71
72Timesheet - Daily Hours Totals
73Tue 10 January 2023From 09 January 2023 to 09 January 2023
74
75BadgePayroll No.NameBasicT/QtrT/HalfDoubleRate05Rate06SickAbsenOther RatesTotal
7600836836NIGEL McCARTHY8:005:300:000:000:000:000:000:000:0013:30
7700838838MATTHEW JAMES FAWCETT7:450:000:000:000:000:000:000:000:007:45
7800843843CHRISTOPHER EMMONDS8:001:000:000:000:000:000:000:000:009:00
7900854854RYAN LACKEY8:000:000:000:000:000:000:000:000:008:00
8000856856KEZIAH JOSEPH PEARSON8:000:000:000:000:000:000:000:000:008:00
8100858858DECLAN REECE SMITH0:000:000:000:000:000:000:000:000:000:00
8200864864AJDIN ZENKOVIC5:450:000:000:000:000:000:000:000:005:45
8300871871MATTHEW THOMAS WALTON8:000:000:000:000:000:000:000:000:008:00
8400890890CHRISTOPHER PEARSON8:000:000:000:000:000:000:000:000:008:00
8500892892GARETH BEALES8:000:000:000:000:000:000:000:000:008:00
8600902902JACK HANLEY0:000:000:000:000:000:000:000:000:000:00
8700903903DAMION CULLUM8:000:000:000:000:000:000:000:000:008:00
8800906906DAVID HARRIS8:000:000:000:000:000:000:000:000:008:00
8900907907ROBERT BEALES8:000:000:000:000:000:000:000:000:008:00
9000909909LIAM LEONARD8:000:000:000:000:000:000:000:000:008:00
9100910910JAMIE MILLS8:000:000:000:000:000:000:000:000:008:00
9200911911ALEXANDER HAYWOOD8:000:000:000:000:000:000:000:000:008:00
9300920920COLIN MICHAEL SILKSTONE8:000:000:000:000:000:000:000:000:008:00
9400923923DECLAN MCLAUGHLIN8:001:150:000:000:000:000:000:000:009:15
9500926926LIAM CURRIE8:000:000:000:000:000:000:000:000:008:00
9600932932JAMES STEVEN8:000:000:000:000:000:000:000:000:008:00
9700936936DAMON MCCORMACK8:000:000:000:000:000:000:000:000:008:00
9800937937LEWIS DAY8:000:000:000:000:000:000:000:000:008:00
9900945945SANDRA VASUI0:000:000:000:000:000:000:000:000:000:00
10000951951DAVID MATYJA8:000:000:000:000:000:000:000:000:008:00
10100953953FRASER ROSS CALDWELL8:000:000:000:000:000:000:000:000:008:00
10200957957SHAUN LENNON8:000:000:000:000:000:000:000:000:008:00
10300958958HARRY HOLMES8:001:000:000:000:000:000:000:000:009:00
104Sub Total :1003:1560:350:000:000:000:000:000:000:001063:50
105
106BasicT/QtrT/HalfDoubleRate05Rate06SickAbsenOther RatesTotal
107Grand Total :1003:1560:350:000:000:000:000:000:000:001063:50
Page1
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Can't see the format in the sample, but should it not be [h]:MM, hence not with round parentheses like in your title?

However you do have totals exceeding 24 hours. So what's the issue then?
 
Upvote 0
Can't see the format in the sample, but should it not be [h]:MM, hence not with round parentheses like in your title?
Hi,GraH,

It shows as in the below image:-

Capture.JPG


If i try to change it as (h):mm in the custom formatting it will not change it to [h}:mm.

Hope that makes sense.
 
Upvote 0
Thanks for the clarification. I still do not fully understand. The format is general. Did you changes it to the custom time format? Does it then revert back to general?
 
Upvote 0
When I change it to the custom time format it will stay as the same. i e. If I did a sum of the column it will show as 0 instead of the cumulative time
 
Upvote 0
See the sum

What version of Excel are you using? Are your regional settings different?

Custom format
select cell N
Control 1
Numbers
Category Custom
Type [h]:mm

Time.xlsm
NO
1
212:00
312:00
412:00
512:00
648:00custom format [h]:mm
11a
Cell Formulas
RangeFormula
N6N6=SUM(N2:N5)
 
Last edited:
Upvote 0
See the sum

What version of Excel are you using? Are your regional settings different?

Custom format
select cell N
Control 1
Numbers
Category Custom
Type [h]:mm

Time.xlsm
NO
1
212:00
312:00
412:00
512:00
648:00custom format [h]:mm
11a
Cell Formulas
RangeFormula
N6N6=SUM(N2:N5)
Hi Dave,

Thanks for your reply. However that does not work.
 
Upvote 0
Remove your merged cells and any other "special" formatting.
Then regular formulas will work.

Drop.xlsm
ABCDEFGHIJKLMN
5BadgePayroll No.NameBasicT/QtrT/HalfDoubleRate05Rate06SickAbsenOther RatesTotal
666GEOFFREY NEIL GREENWOOD08:0001:0000:0000:0000:0000:0000:0000:0000:0009:00
740607GARY STEVEN VINE08:0003:1500:0000:0000:0000:0000:0000:0000:0011:15
84242ROBERT MOUNTAIN08:0002:3000:0000:0000:0000:0000:0000:0000:0010:30
99494ALADIN BOLIC05:1500:0000:0000:0000:0000:0000:0000:0000:0005:15
10113113JOHN DAVID MASSINGHAM00:0000:0000:0000:0000:0000:0000:0000:0000:0000:00
11131131MARK ANTHONY SPENCER08:0004:4500:0000:0000:0000:0000:0000:0000:0012:45
12200200MARTIN JOHN WALKER08:0000:3000:0000:0000:0000:0000:0000:0000:0008:30
13202202ANDREW SENIOR00:0000:0000:0000:0000:0000:0000:0000:0000:0000:00
14217217GARY MADDOX08:0000:0000:0000:0000:0000:0000:0000:0000:0008:00
15250250PAUL DAY08:0000:0000:0000:0000:0000:0000:0000:0000:0008:00
16289289PAUL IAN HEWITT08:0001:0000:0000:0000:0000:0000:0000:0000:0009:00
17295295NEIL RIDDLE08:0000:0000:0000:0000:0000:0000:0000:0000:0008:00
18333333STUART ANDREW BROADBENT08:0000:0000:0000:0000:0000:0000:0000:0000:0008:00
19341341MARIA NOEMI DE FREIT JOAO08:0001:0000:0000:0000:0000:0000:0000:0000:0009:00
20344344ADRIAN PAUL CHAPMAN08:0000:0000:0000:0000:0000:0000:0000:0000:0008:00
21353353LEON BEMPA OWUSU08:0003:1500:0000:0000:0000:0000:0000:0000:0011:15
22368368CHRISTOPHER KEAR00:0000:0000:0000:0000:0000:0000:0000:0000:0000:00
23384384STEFAN WOLOSZYN08:0000:0000:0000:0000:0000:0000:0000:0000:0008:00
24463463JOHN MARTIN FISHER08:0000:0000:0000:0000:0000:0000:0000:0000:0008:00
25471471ARRON ASHLEY THOMAS08:0000:4500:0000:0000:0000:0000:0000:0000:0008:45
26487487RICHMOND OFOSU08:0002:1500:0000:0000:0000:0000:0000:0000:0010:15
27501501CHRISTOPHER MATTHEW GRAY08:0000:0000:0000:0000:0000:0000:0000:0000:0008:00
28512512KEITH SMITH08:0000:0000:0000:0000:0000:0000:0000:0000:0008:00
29514514TROY KIRKLAND PHILLIP08:0000:0000:0000:0000:0000:0000:0000:0000:0008:00
30525525RUSSELL SPENCER08:0000:0000:0000:0000:0000:0000:0000:0000:0008:00
31565565STEVEN IVANKIV08:0000:0000:0000:0000:0000:0000:0000:0000:0008:00
32585585ADAM FIRTH08:0000:0000:0000:0000:0000:0000:0000:0000:0008:00
33614614JASON WADE08:0001:0000:0000:0000:0000:0000:0000:0000:0009:00
34218:30
1f
Cell Formulas
RangeFormula
N34N34=SUM(N6:N33)
 
Upvote 0
You did not answer my question. What version of Excel are you use using and what is your operating system?

There is also an hour limit that will show with [h]:mm

A concise example that illustrates your challenge is much better that a large post of data.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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