# Time formula

#### mahmed1

##### Well-known Member
Hi,

I wanted to know that if i worked 14:78 or 13:82 hours in a month etc...

What is the formula to work out exactly how many minutes 82 are. I can work it out in my head but i wanted know exactly what 82: works out to be? Would i be right in saying 13:50 is 13 and a half hours?

Thanks

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Andrew Poulsom

##### MrExcel MVP
You can't have 14:78 or 13:82, because the minutes stop at 59 and the hour increases. No 13:50 is 13 hours and 50 minutes. 13:30 would be 13 and a half hours. If you have a time like 13:30 in A1 you can convert it to decimal hours like this:

=A1*24

formatted as General.

#### mahmed1

##### Well-known Member
You can't have 14:78 or 13:82, because the minutes stop at 59 and the hour increases. No 13:50 is 13 hours and 50 minutes. 13:30 would be 13 and a half hours. If you have a time like 13:30 in A1 you can convert it to decimal hours like this:

=A1*24

formatted as General.

Cool thank you

I was working out my holiday entitlement in hours and days.

i get 25 days normally * (33 hours which i will be working weekly) / full time working hours (37 hours) which gives 22.29 days ( i need a formula to show me what the .29 represnts in minutes, would that be 29 minutes?

22.29 * 7.5 (hours working day) = 167.22

I needed a formula to change say it was 167.63 (Hours owed and minutes owed) i.e 168 hours and 3 minutes etc..i thought 167.5 was 167 and half hours but i guess i was wrong

Hope that makes sense

#### Andrew Poulsom

##### MrExcel MVP
This:

=22.29*7.5/24

formatted as [hh:mm] returns 167:10 (167 hours and 10 minutes). Decimal hours would be 167.175, and 0.175 of an hour is 10 minutes.

#### mahmed1

##### Well-known Member
This:

=22.29*7.5/24

formatted as [hh:mm] returns 167:10 (167 hours and 10 minutes). Decimal hours would be 167.175, and 0.175 of an hour is 10 minutes.

How would i just work out the decimal part to see what it would equate to in minutes?

i.e 0.175?

Another thing what formula can i have to change 17 hours and 63 minutes to 18 hours and 3 minutes?

#### Andrew Poulsom

##### MrExcel MVP
The decimal part is:

=MOD(22.29*7.5,1)

but you can see it quite easily with just:

=22.29*7.5

You said it was 167.22, but I expect that's a rounding issue.

If A1 contains 17 (hours) and B1 contains 63 (minutes) then:

=(A1/24)+(B1/(24*60))

will return 18:03 when formatted as hh:mm.

In Excel time is just decimals of a day formatted in a special way. So 12 noon is 0.5 (half a day) when formatted as general.

Replies
26
Views
2K
Replies
7
Views
120
Replies
4
Views
96
Replies
0
Views
67
Replies
3
Views
48