# Cannot format cells into (h):mm



## shina67 (Tuesday at 2:02 PM)

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.xlsxABCDEFGHIJKLMN12Timesheet - Daily Hours Totals3Tue 10 January 2023From 09 January 2023 to 09 January 202345BadgePayroll No.NameBasicT/QtrT/HalfDoubleRate05Rate06SickAbsenOther RatesTotal6000066GEOFFREY NEIL GREENWOOD8:001:000:000:000:000:000:000:000:009:00700040607GARY STEVEN VINE8:003:150:000:000:000:000:000:000:0011:1580004242ROBERT MOUNTAIN8:002:300:000:000:000:000:000:000:0010:3090009494ALADIN BOLIC5:150:000:000:000:000:000:000:000:005:151000113113JOHN DAVID MASSINGHAM0:000:000:000:000:000:000:000:000:000:001100131131MARK ANTHONY SPENCER8:004:450:000:000:000:000:000:000:0012:451200200200MARTIN JOHN WALKER8:000:300:000:000:000:000:000:000:008:301300202202ANDREW SENIOR0:000:000:000:000:000:000:000:000:000:001400217217GARY MADDOX8:000:000:000:000:000:000:000:000:008:001500250250PAUL DAY8:000:000:000:000:000:000:000:000:008:001600289289PAUL IAN HEWITT8:001:000:000:000:000:000:000:000:009:001700295295NEIL RIDDLE8:000:000:000:000:000:000:000:000:008:001800333333STUART ANDREW BROADBENT8:000:000:000:000:000:000:000:000:008:001900341341MARIA NOEMI DE FREIT JOAO8:001:000:000:000:000:000:000:000:009:002000344344ADRIAN PAUL CHAPMAN8:000:000:000:000:000:000:000:000:008:002100353353LEON BEMPA OWUSU8:003:150:000:000:000:000:000:000:0011:152200368368CHRISTOPHER KEAR0:000:000:000:000:000:000:000:000:000:002300384384STEFAN WOLOSZYN8:000:000:000:000:000:000:000:000:008:002400463463JOHN MARTIN FISHER8:000:000:000:000:000:000:000:000:008:002500471471ARRON ASHLEY THOMAS8:000:450:000:000:000:000:000:000:008:452600487487RICHMOND OFOSU8:002:150:000:000:000:000:000:000:0010:152700501501CHRISTOPHER MATTHEW  GRAY8:000:000:000:000:000:000:000:000:008:002800512512KEITH SMITH8:000:000:000:000:000:000:000:000:008:002900514514TROY KIRKLAND PHILLIP8:000:000:000:000:000:000:000:000:008:003000525525RUSSELL  SPENCER8:000:000:000:000:000:000:000:000:008:003100565565STEVEN IVANKIV8:000:000:000:000:000:000:000:000:008:003200585585ADAM FIRTH8:000:000:000:000:000:000:000:000:008:003300614614JASON  WADE8:001:000:000:000:000:000:000:000:009:00343504/005313637Timesheet - Daily Hours Totals38Tue 10 January 2023From 09 January 2023 to 09 January 20233940BadgePayroll No.NameBasicT/QtrT/HalfDoubleRate05Rate06SickAbsenOther RatesTotal4100628628JAMES  NEWMAN8:000:000:000:000:000:000:000:000:008:004200638638MARK SCOTT8:000:000:000:000:000:000:000:000:008:004300641641DAVID OLIVER8:000:000:000:000:000:000:000:000:008:004400660660JASON MITCHELL8:000:000:000:000:000:000:000:000:008:004500678678LOUIE WHITE8:000:000:000:000:000:000:000:000:008:004600684684DEAN STANLEY8:000:000:000:000:000:000:000:000:008:004700688688SENAD IMEROSKI8:000:000:000:000:000:000:000:000:008:004800690690BRANDON ROBERTSON8:000:000:000:000:000:000:000:000:008:004900694694THOMAS JAKE LUNN0:000:000:000:000:000:000:000:000:000:005000702702SCOTT WATSON0:000:000:000:000:000:000:000:000:000:005100710710STEVEN COOK7:450:000:000:000:000:000:000:000:007:455200711711DARREN JOSEPH SANDERS8:000:000:000:000:000:000:000:000:008:005300716716RUI DE ABREU8:002:450:000:000:000:000:000:000:0010:4554007180718JODIE TOLLERFIELD0:000:000:000:000:000:000:000:000:000:005500728728EVANS OFORI8:000:300:000:000:000:000:000:000:008:305600733733LIAM CHEALE8:000:000:000:000:000:000:000:000:008:005700755755DANIEL CRAIG WHITELEY8:000:000:000:000:000:000:000:000:008:005800757757LOUISE MICHELLE GRESTY8:000:000:000:000:000:000:000:000:008:005900760760HAYDN BOWLER0:000:000:000:000:000:000:000:000:000:006000767767ALLAN WALTERS7:300:000:000:000:000:000:000:000:007:306100770770ADAM DAVIS8:000:000:000:000:000:000:000:000:008:006200787787JONATHON PAUL COWLING8:001:000:000:000:000:000:000:000:009:006300796796JAMIE SHAW8:000:000:000:000:000:000:000:000:008:006400801801NEIL LORIMER8:001:000:000:000:000:000:000:000:009:006500817817DANIEL JENNINGS8:003:150:000:000:000:000:000:000:0011:156600826826KYLE EMMONDS8:001:000:000:000:000:000:000:000:009:006700828828JOHN MORRELL8:000:000:000:000:000:000:000:000:008:006800834834ALEX ALHASSAN8:000:000:000:000:000:000:000:000:008:00697004/005327172Timesheet - Daily Hours Totals73Tue 10 January 2023From 09 January 2023 to 09 January 20237475BadgePayroll No.NameBasicT/QtrT/HalfDoubleRate05Rate06SickAbsenOther RatesTotal7600836836NIGEL McCARTHY8:005:300:000:000:000:000:000:000:0013:307700838838MATTHEW JAMES FAWCETT7:450:000:000:000:000:000:000:000:007:457800843843CHRISTOPHER EMMONDS8:001:000:000:000:000:000:000:000:009:007900854854RYAN LACKEY8:000:000:000:000:000:000:000:000:008:008000856856KEZIAH JOSEPH PEARSON8:000:000:000:000:000:000:000:000:008:008100858858DECLAN REECE SMITH0:000:000:000:000:000:000:000:000:000:008200864864AJDIN ZENKOVIC5:450:000:000:000:000:000:000:000:005:458300871871MATTHEW THOMAS WALTON8:000:000:000:000:000:000:000:000:008:008400890890CHRISTOPHER PEARSON8:000:000:000:000:000:000:000:000:008:008500892892GARETH BEALES8:000:000:000:000:000:000:000:000:008:008600902902JACK HANLEY0:000:000:000:000:000:000:000:000:000:008700903903DAMION CULLUM8:000:000:000:000:000:000:000:000:008:008800906906DAVID HARRIS8:000:000:000:000:000:000:000:000:008:008900907907ROBERT BEALES8:000:000:000:000:000:000:000:000:008:009000909909LIAM LEONARD8:000:000:000:000:000:000:000:000:008:009100910910JAMIE MILLS8:000:000:000:000:000:000:000:000:008:009200911911ALEXANDER HAYWOOD8:000:000:000:000:000:000:000:000:008:009300920920COLIN MICHAEL SILKSTONE8:000:000:000:000:000:000:000:000:008:009400923923DECLAN MCLAUGHLIN8:001:150:000:000:000:000:000:000:009:159500926926LIAM CURRIE8:000:000:000:000:000:000:000:000:008:009600932932JAMES STEVEN8:000:000:000:000:000:000:000:000:008:009700936936DAMON MCCORMACK8:000:000:000:000:000:000:000:000:008:009800937937LEWIS DAY8:000:000:000:000:000:000:000:000:008:009900945945SANDRA VASUI0:000:000:000:000:000:000:000:000:000:0010000951951DAVID MATYJA8:000:000:000:000:000:000:000:000:008:0010100953953FRASER ROSS CALDWELL8:000:000:000:000:000:000:000:000:008:0010200957957SHAUN  LENNON8:000:000:000:000:000:000:000:000:008:0010300958958HARRY HOLMES8:001:000:000:000:000:000:000:000:009:00104Sub Total :1003:1560:350:000:000:000:000:000:000:001063:50105106BasicT/QtrT/HalfDoubleRate05Rate06SickAbsenOther RatesTotal107Grand Total :1003:1560:350:000:000:000:000:000:000:001063:50Page1


----------



## GraH (Tuesday at 3:00 PM)

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?


----------



## shina67 (Tuesday at 3:07 PM)

GraH said:


> 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:-






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.


----------



## GraH (Tuesday at 3:13 PM)

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?


----------



## shina67 (Tuesday at 3:25 PM)

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


----------



## shina67 (Tuesday at 3:29 PM)

GraH said:


> 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?


GraH I have sent you a message


----------



## Dave Patton (Tuesday at 3:34 PM)

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.xlsmNO1212:00312:00412:00512:00648:00custom format [h]:mm11aCell FormulasRangeFormulaN6N6=SUM(N2:N5)


----------



## shina67 (Tuesday at 3:55 PM)

Dave Patton said:


> See the sum
> 
> What version of  Excel are you using?  Are your regional settings different?
> 
> ...


Hi Dave,

Thanks for your reply. However that does not work.


----------



## Dave Patton (Tuesday at 4:23 PM)

Remove your merged cells and any other "special" formatting.
Then regular formulas will work.

Drop.xlsmABCDEFGHIJKLMN5BadgePayroll No.NameBasicT/QtrT/HalfDoubleRate05Rate06SickAbsenOther RatesTotal666GEOFFREY NEIL GREENWOOD08:0001:0000:0000:0000:0000:0000:0000:0000:0009:00740607GARY STEVEN VINE08:0003:1500:0000:0000:0000:0000:0000:0000:0011:1584242ROBERT MOUNTAIN08:0002:3000:0000:0000:0000:0000:0000:0000:0010:3099494ALADIN BOLIC05:1500:0000:0000:0000:0000:0000:0000:0000:0005:1510113113JOHN DAVID MASSINGHAM00:0000:0000:0000:0000:0000:0000:0000:0000:0000:0011131131MARK ANTHONY SPENCER08:0004:4500:0000:0000:0000:0000:0000:0000:0012:4512200200MARTIN JOHN WALKER08:0000:3000:0000:0000:0000:0000:0000:0000:0008:3013202202ANDREW SENIOR00:0000:0000:0000:0000:0000:0000:0000:0000:0000:0014217217GARY MADDOX08:0000:0000:0000:0000:0000:0000:0000:0000:0008:0015250250PAUL DAY08:0000:0000:0000:0000:0000:0000:0000:0000:0008:0016289289PAUL IAN HEWITT08:0001:0000:0000:0000:0000:0000:0000:0000:0009:0017295295NEIL RIDDLE08:0000:0000:0000:0000:0000:0000:0000:0000:0008:0018333333STUART ANDREW BROADBENT08:0000:0000:0000:0000:0000:0000:0000:0000:0008:0019341341MARIA NOEMI DE FREIT JOAO08:0001:0000:0000:0000:0000:0000:0000:0000:0009:0020344344ADRIAN PAUL CHAPMAN08:0000:0000:0000:0000:0000:0000:0000:0000:0008:0021353353LEON BEMPA OWUSU08:0003:1500:0000:0000:0000:0000:0000:0000:0011:1522368368CHRISTOPHER KEAR00:0000:0000:0000:0000:0000:0000:0000:0000:0000:0023384384STEFAN WOLOSZYN08:0000:0000:0000:0000:0000:0000:0000:0000:0008:0024463463JOHN MARTIN FISHER08:0000:0000:0000:0000:0000:0000:0000:0000:0008:0025471471ARRON ASHLEY THOMAS08:0000:4500:0000:0000:0000:0000:0000:0000:0008:4526487487RICHMOND OFOSU08:0002:1500:0000:0000:0000:0000:0000:0000:0010:1527501501CHRISTOPHER MATTHEW  GRAY08:0000:0000:0000:0000:0000:0000:0000:0000:0008:0028512512KEITH SMITH08:0000:0000:0000:0000:0000:0000:0000:0000:0008:0029514514TROY KIRKLAND PHILLIP08:0000:0000:0000:0000:0000:0000:0000:0000:0008:0030525525RUSSELL  SPENCER08:0000:0000:0000:0000:0000:0000:0000:0000:0008:0031565565STEVEN IVANKIV08:0000:0000:0000:0000:0000:0000:0000:0000:0008:0032585585ADAM FIRTH08:0000:0000:0000:0000:0000:0000:0000:0000:0008:0033614614JASON  WADE08:0001:0000:0000:0000:0000:0000:0000:0000:0009:0034218:301fCell FormulasRangeFormulaN34N34=SUM(N6:N33)


----------



## Dave Patton (Tuesday at 4:46 PM)

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.


----------



## shina67 (Tuesday at 2:02 PM)

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.xlsxABCDEFGHIJKLMN12Timesheet - Daily Hours Totals3Tue 10 January 2023From 09 January 2023 to 09 January 202345BadgePayroll No.NameBasicT/QtrT/HalfDoubleRate05Rate06SickAbsenOther RatesTotal6000066GEOFFREY NEIL GREENWOOD8:001:000:000:000:000:000:000:000:009:00700040607GARY STEVEN VINE8:003:150:000:000:000:000:000:000:0011:1580004242ROBERT MOUNTAIN8:002:300:000:000:000:000:000:000:0010:3090009494ALADIN BOLIC5:150:000:000:000:000:000:000:000:005:151000113113JOHN DAVID MASSINGHAM0:000:000:000:000:000:000:000:000:000:001100131131MARK ANTHONY SPENCER8:004:450:000:000:000:000:000:000:0012:451200200200MARTIN JOHN WALKER8:000:300:000:000:000:000:000:000:008:301300202202ANDREW SENIOR0:000:000:000:000:000:000:000:000:000:001400217217GARY MADDOX8:000:000:000:000:000:000:000:000:008:001500250250PAUL DAY8:000:000:000:000:000:000:000:000:008:001600289289PAUL IAN HEWITT8:001:000:000:000:000:000:000:000:009:001700295295NEIL RIDDLE8:000:000:000:000:000:000:000:000:008:001800333333STUART ANDREW BROADBENT8:000:000:000:000:000:000:000:000:008:001900341341MARIA NOEMI DE FREIT JOAO8:001:000:000:000:000:000:000:000:009:002000344344ADRIAN PAUL CHAPMAN8:000:000:000:000:000:000:000:000:008:002100353353LEON BEMPA OWUSU8:003:150:000:000:000:000:000:000:0011:152200368368CHRISTOPHER KEAR0:000:000:000:000:000:000:000:000:000:002300384384STEFAN WOLOSZYN8:000:000:000:000:000:000:000:000:008:002400463463JOHN MARTIN FISHER8:000:000:000:000:000:000:000:000:008:002500471471ARRON ASHLEY THOMAS8:000:450:000:000:000:000:000:000:008:452600487487RICHMOND OFOSU8:002:150:000:000:000:000:000:000:0010:152700501501CHRISTOPHER MATTHEW  GRAY8:000:000:000:000:000:000:000:000:008:002800512512KEITH SMITH8:000:000:000:000:000:000:000:000:008:002900514514TROY KIRKLAND PHILLIP8:000:000:000:000:000:000:000:000:008:003000525525RUSSELL  SPENCER8:000:000:000:000:000:000:000:000:008:003100565565STEVEN IVANKIV8:000:000:000:000:000:000:000:000:008:003200585585ADAM FIRTH8:000:000:000:000:000:000:000:000:008:003300614614JASON  WADE8:001:000:000:000:000:000:000:000:009:00343504/005313637Timesheet - Daily Hours Totals38Tue 10 January 2023From 09 January 2023 to 09 January 20233940BadgePayroll No.NameBasicT/QtrT/HalfDoubleRate05Rate06SickAbsenOther RatesTotal4100628628JAMES  NEWMAN8:000:000:000:000:000:000:000:000:008:004200638638MARK SCOTT8:000:000:000:000:000:000:000:000:008:004300641641DAVID OLIVER8:000:000:000:000:000:000:000:000:008:004400660660JASON MITCHELL8:000:000:000:000:000:000:000:000:008:004500678678LOUIE WHITE8:000:000:000:000:000:000:000:000:008:004600684684DEAN STANLEY8:000:000:000:000:000:000:000:000:008:004700688688SENAD IMEROSKI8:000:000:000:000:000:000:000:000:008:004800690690BRANDON ROBERTSON8:000:000:000:000:000:000:000:000:008:004900694694THOMAS JAKE LUNN0:000:000:000:000:000:000:000:000:000:005000702702SCOTT WATSON0:000:000:000:000:000:000:000:000:000:005100710710STEVEN COOK7:450:000:000:000:000:000:000:000:007:455200711711DARREN JOSEPH SANDERS8:000:000:000:000:000:000:000:000:008:005300716716RUI DE ABREU8:002:450:000:000:000:000:000:000:0010:4554007180718JODIE TOLLERFIELD0:000:000:000:000:000:000:000:000:000:005500728728EVANS OFORI8:000:300:000:000:000:000:000:000:008:305600733733LIAM CHEALE8:000:000:000:000:000:000:000:000:008:005700755755DANIEL CRAIG WHITELEY8:000:000:000:000:000:000:000:000:008:005800757757LOUISE MICHELLE GRESTY8:000:000:000:000:000:000:000:000:008:005900760760HAYDN BOWLER0:000:000:000:000:000:000:000:000:000:006000767767ALLAN WALTERS7:300:000:000:000:000:000:000:000:007:306100770770ADAM DAVIS8:000:000:000:000:000:000:000:000:008:006200787787JONATHON PAUL COWLING8:001:000:000:000:000:000:000:000:009:006300796796JAMIE SHAW8:000:000:000:000:000:000:000:000:008:006400801801NEIL LORIMER8:001:000:000:000:000:000:000:000:009:006500817817DANIEL JENNINGS8:003:150:000:000:000:000:000:000:0011:156600826826KYLE EMMONDS8:001:000:000:000:000:000:000:000:009:006700828828JOHN MORRELL8:000:000:000:000:000:000:000:000:008:006800834834ALEX ALHASSAN8:000:000:000:000:000:000:000:000:008:00697004/005327172Timesheet - Daily Hours Totals73Tue 10 January 2023From 09 January 2023 to 09 January 20237475BadgePayroll No.NameBasicT/QtrT/HalfDoubleRate05Rate06SickAbsenOther RatesTotal7600836836NIGEL McCARTHY8:005:300:000:000:000:000:000:000:0013:307700838838MATTHEW JAMES FAWCETT7:450:000:000:000:000:000:000:000:007:457800843843CHRISTOPHER EMMONDS8:001:000:000:000:000:000:000:000:009:007900854854RYAN LACKEY8:000:000:000:000:000:000:000:000:008:008000856856KEZIAH JOSEPH PEARSON8:000:000:000:000:000:000:000:000:008:008100858858DECLAN REECE SMITH0:000:000:000:000:000:000:000:000:000:008200864864AJDIN ZENKOVIC5:450:000:000:000:000:000:000:000:005:458300871871MATTHEW THOMAS WALTON8:000:000:000:000:000:000:000:000:008:008400890890CHRISTOPHER PEARSON8:000:000:000:000:000:000:000:000:008:008500892892GARETH BEALES8:000:000:000:000:000:000:000:000:008:008600902902JACK HANLEY0:000:000:000:000:000:000:000:000:000:008700903903DAMION CULLUM8:000:000:000:000:000:000:000:000:008:008800906906DAVID HARRIS8:000:000:000:000:000:000:000:000:008:008900907907ROBERT BEALES8:000:000:000:000:000:000:000:000:008:009000909909LIAM LEONARD8:000:000:000:000:000:000:000:000:008:009100910910JAMIE MILLS8:000:000:000:000:000:000:000:000:008:009200911911ALEXANDER HAYWOOD8:000:000:000:000:000:000:000:000:008:009300920920COLIN MICHAEL SILKSTONE8:000:000:000:000:000:000:000:000:008:009400923923DECLAN MCLAUGHLIN8:001:150:000:000:000:000:000:000:009:159500926926LIAM CURRIE8:000:000:000:000:000:000:000:000:008:009600932932JAMES STEVEN8:000:000:000:000:000:000:000:000:008:009700936936DAMON MCCORMACK8:000:000:000:000:000:000:000:000:008:009800937937LEWIS DAY8:000:000:000:000:000:000:000:000:008:009900945945SANDRA VASUI0:000:000:000:000:000:000:000:000:000:0010000951951DAVID MATYJA8:000:000:000:000:000:000:000:000:008:0010100953953FRASER ROSS CALDWELL8:000:000:000:000:000:000:000:000:008:0010200957957SHAUN  LENNON8:000:000:000:000:000:000:000:000:008:0010300958958HARRY HOLMES8:001:000:000:000:000:000:000:000:009:00104Sub Total :1003:1560:350:000:000:000:000:000:000:001063:50105106BasicT/QtrT/HalfDoubleRate05Rate06SickAbsenOther RatesTotal107Grand Total :1003:1560:350:000:000:000:000:000:000:001063:50Page1


----------



## shina67 (Tuesday at 11:53 PM)

Microsoft Office 365 Enterprise.

Windows 10 Pro

Dave I think the problem is that the data is as text and not numerical.


----------



## GraH (Wednesday at 1:24 AM)

Try with text to column or import the data in another excel with Power Query. The latter is what I'd use as it allows to standardize the data on the fly.


----------



## Dave Patton (Wednesday at 5:20 AM)

See post # 9

Remove your merged cells and any other "special" formatting.
Then regular formulas will work.

I removed the merged cells and hidden information and then entered the sum formula.
The formula with custom format showed the results.


----------



## Fluff (Wednesday at 6:45 AM)

shina67 said:


> Microsoft Office 365 Enterprise.
> 
> Windows 10 Pro



I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


----------



## shina67 (Wednesday at 12:13 PM)

Problem solved. I used the value() function and then formatted the cells to (h):mm.


----------

