Time formula

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,172
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Mar 28, 2009
Messages
2,172
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Mar 28, 2009
Messages
2,172
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Jul 21, 2002
Messages
73,092
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,918
Messages
5,525,625
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top