converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes & se

**tigerpie** · 05-03-2013, 10:20 AM

Hello,
I have a spread sheet where I have been recording the days / hours I have worked. I am also trying ti calculate the amount of holidays I have accrued.
However when I first started using excel I entered everything in decimal numbers and now I am struggling to convert everything to hours and minutes and seconds.
I have attached a demo spreadsheet, but basically I would like to know, if i have a cell that has a decimal 8 (cell A1) in it to represent 8 hours and another cell with the formula =A1*4.56 how do i get the result to be hours:minutes

test book.xls

**popipipo** · 05-03-2013, 10:33 AM

Please Login or Register  to view this content.

**bebo021999** · 05-03-2013, 10:37 AM

Note that Excel store:
1 day = 1
1 hour = 1/24
1 minute = 1/1440
To enter 8:00 in C3, just type: 8:00 and format as time
To multiply 4.56 mins by 8 hour: First convert 8:00 to number (C3*24), then convert 4.56 min to number (4.56/1440), then multiply the two number together:
In D3: =C3*24*4.56/1440
format as time.
Hope it is clear for you.

EDIT: The correct one should be:
=C3*24*(4/1440+56/86400)
bcz I have to convert 56 sec to number by divide to 86400 (=24*60*60)

**Kevin UK** · 05-03-2013, 11:12 AM

Hi tigerpie

Have a look at the attached file. Column C.

**bebo021999** · 05-03-2013, 11:23 AM

Kevin, 4.56 means 4 minute and 56 seconds, so can not use 4.56/1440 to convert?

**tigerpie** · 05-03-2013, 11:42 AM

HI Guys thanks for that.. I think I may need help clarifying something first.
For a 38hr week I accrue 2hrs and 55 mins of annual leave.. so the maths I was trying to work out the amount of hours (or minutes) of Annual Leave accrue per day (8hrs) was
2hrs & 55mins = 175mins
38hrs x 60 = 2280mins
and now to be honest, I forgot how my friend explained it to me, and he has gone to bed... Would anybody know?
that way I can clarify if 4.56 means 4mins and 56secs or 4mins and .56 of a minute (just over half a minute)
Sorry if I have confused anyone.

**Kevin UK** · 05-03-2013, 11:45 AM

@ bebo021999

Type = 4.5/1440 in a cell, format as mm:ss and tell me what the result is!

Try here

**daddylonglegs** · 05-03-2013, 11:48 AM

I'm not sure where 4.56 comes in at all...

If you accrue 175 minutes for 38 hours work then in 8 hours you would accrue (175/38)*8 which is 36.84211 minutes as a decimal which is 36:50.526 as a time value (approximately 36 minutes 50.5 secs)

**Kevin UK** · 05-03-2013, 11:52 AM

@ daddylonglegs

By the OP's spread sheet, they had already calculated that themselves. The question was to convert to hh:mm etc

**tigerpie** · 05-03-2013, 11:53 AM

the 4.56 comes in as a figure per hour..
I don't understand 175mins / 38hours x 8 hours?
before you divide the 175mins by the weekly hours, don't you have to convert the 38hrs to minutes so you are working with the same units?

**bebo021999** · 05-03-2013, 12:17 PM

Originally Posted by Kevin UK

@ bebo021999
Type = 4.5/1440 in a cell, format as mm:ss and tell me what the result is!
Try here

Kevin,
OP said 4.56 = 4h and 56 second==> convert to number: (4/1440+56/86400)
mean 4h and 56/60 hour
With your way, it means 4h and 56/100 hour==>convert to number: 4.56/1440
Quite different?

**Kevin UK** · 05-03-2013, 12:22 PM

@ bebo021999

Then I suggest you open the file up again and look in cell D1!

**daddylonglegs** · 05-03-2013, 01:57 PM

I'm suggesting 4.56 is wrong

175/38=4.6 minutes, as a time value that's approx 4:36

Try this for your spreadsheet:

Put the accrual amount for a week in A8 like this: 2:55
Now in A9 use this formula for the hourly accrual

=A8/38

format A9 as h:mm:ss and you should see 0:04:36

[Note that's just the displayed amount, there are also fractions of seconds in the underlying value that will be used in any calculation]

Now in D3 you can use this formula to get the accrual amount for the number of hours in C3

=C3*A$9

again, format D3 as h:mm:ss and copy formula down column - I get 0:36:51 in D3 (which is the same as the amount I suggested in post #8 but rounded to the nearest second)

Does that do what you want?

**tigerpie** · 05-03-2013, 08:42 PM

Hi Guys,
thank you all for the help you have given me.
This is great stuff and now I am confident I will get all of my figures correct.
Thanks heaps for this guys, its very much appreciated :-)

converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes & se

LinkBack

Thread Tools

Rate This Thread

Display

converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes & se

Re: converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes

Re: converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes

Re: converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes

Re: converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes

Re: converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes

Re: converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes

Re: converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes

Re: converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes

Re: converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes

Re: converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes

Re: converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes

Re: converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes

Re: converting decimal numbers to hours & Minutes & Secs and multiplying hours by minutes

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions