Turn a number into hour and minutes

M

madforgolf

Board Regular
Joined
Oct 20, 2006
Messages
180
Office Version
  1. 365
  2. 2016
Hi
I have a query, trying to apply a format where i have a total numers of hours as my holiday entitlement.i want to be able to deduct hours and minutes and have a total =hours and minutes.
EG -- i have 192 hours to take in a3 from b3-x3 is where i place the time i have taken and total is in y3. if i take 6h and 45 mins from 192 how do i get it to work in y3.
I keep getting 00:00 instead of h:mm. it should be simple- i just cant see it.
Thanks in advance.
Marty
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Brian from Maui

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
It depends on how 192 is formatted. The cell that houses 192 should be formatted a [h]:mm. Conversely the cell that houses the addition/subtraction should also be formatted at such.

Not to forget 6hrs 45 min should be entered as 6:45.
 
Upvote 0
Y

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi madforgolf:

To elaborate what Brian has stated, let us look at the following ...

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWXY
3192:0002:1501:1003:20********************185:15
Sheet5


</body></html>

all the entries are CustomNumberFormat<i>ed as [hh]:mm
 
Upvote 0
M

madforgolf

Board Regular
Joined
Oct 20, 2006
Messages
180
Office Version
  1. 365
  2. 2016
Thanks for the replies, i will test these b4 5:00am an will post back.
Regards marty.
 
Upvote 0
M

madforgolf

Board Regular
Joined
Oct 20, 2006
Messages
180
Office Version
  1. 365
  2. 2016
Hi
when i format the cell to [h]:mm.(192 turns to 4608:00 ) with a date in the forumla bar.
Also do i need to apply the format to each cell in the row?

A B C D E F G H I J K L M N O P Q R S T U V W X Y
3 192:00 02:15 01:10 03:20 * * * * * * * * * * * * * * * * * * * * 185:15

Does the * mean anything, what have i missed.

Marty
 
Upvote 0
Y

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
madforgolf said:
Hi
when i format the cell to [h]:mm.(192 turns to 4608:00 ) with a date in the forumla bar.
Also do i need to apply the format to each cell in the row?

A B C D E F G H I J K L M N O P Q R S T U V W X Y
3 192:00 02:15 01:10 03:20 * * * * * * * * * * * * * * * * * * * * 185:15

Does the * mean anything, what have i missed.

Marty
Click to expand...

Hi Marty:

In cell A3 if you enter 192, EXCEL understands it to be 192 days ... so in cell A3, key-in

either
1) 192:00
or
2) =192/24

I hope this helps.
 
Upvote 0
M

madforgolf

Board Regular
Joined
Oct 20, 2006
Messages
180
Office Version
  1. 365
  2. 2016
Yogi Anand

Thanks for that --- work 100%. This is THE place to get results.

thank you everyone.

marty
 
Upvote 0
You must log in or register to reply here.

Similar threads

JEH105
  • Solved
IF NOW is between specific timeframes not counting both HOUR and MINUTE - HELP PLS :)
Replies
5
Views
87
Fluff
Fluff
J
  • Question
Sum hour between two times
Replies
1
Views
74
Kerryx
K
H
  • Solved
Breaking down the labor cost into per minute under the hourly heady
Replies
3
Views
249
JamesCanale
JamesCanale
S
  • Solved
Conditional formatting to visually gauge progress towards target value
Replies
1
Views
152
DanteAmor
DanteAmor
D
  • Solved
"If" formula with mix of text and number to identify full days absent, or only hours absent
Replies
5
Views
80
Fluff
Fluff

Forum statistics

Threads
1,190,885
Messages
5,983,395
Members
439,842
Latest member
grammenmg5

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
Top