Hours to minutes - not getting expected result

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
843
Office Version
  1. 365
Platform
  1. Windows
On my spread sheet in cell R11 (and others), I have set up a formula to total up hours and minutes and this is working fine. R11 is formatted as [h]:mm as the total often exceeds 24. In cell T11 I am trying to convert the total in R11 to minutes by using the formula =HOUR(R11)*60 + MINUTE*(R11)/60 but I am not getting the expected result. Can anyone explain what is wrong, please? In this instance R11 = 25:00 and I expected T11 to show 1500:00 but instead it displays 1440:00

Thanks for your help.

Mel
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,790
Office Version
  1. 365
Platform
  1. MacOS
multiply the time by 1440
No of Minutes in a day 24*60
and format the cell as a number

chemistry_v1.xlsx
QRSTUVWXY
10
1125:001500
12
13
14R11 = 25:00 and I expected T11 to show 1500:00 but instead it displays 1440:00
15
Sheet14
Cell Formulas
RangeFormula
T11T11=R11*1440
 

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
843
Office Version
  1. 365
Platform
  1. Windows
It's obvious when you see it!

Many thanks,

Mel
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
Can anyone explain what is wrong, please? In this instance R11 = 25:00
In relation to this question: 25:00 is one day and 1 hour, so your HOUR function will return 1, not 25.
 

Forum statistics

Threads
1,136,644
Messages
5,676,967
Members
419,665
Latest member
allsmilees2

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