Forum Discussion

Copper Contributor

Oct 09, 2023

Solved

How to work out hours from number answer?

Hi, I am doing a service swap with a client. So I owe him £250 but I charge £35 per hour for my work. How many hours do I owe him? When I work it out in excel it 250/35 = 7.142857143 but what formul...

excel

Formulas and Functions

NikolinoDE
Oct 09, 2023
lionheartva
To convert the decimal portion (the .14 in your case) into hours and minutes, you can use the following Excel formula:
Assuming that your decimal value is in cell A1, you can use the following formula to convert it into hours and minutes:
=INT(A1) & " hours and " & TEXT((A1-INT(A1))*60,"0") & " minutes"
Here's how it works:
INT(A1) extracts the whole number part, which represents hours.
(A1-INT(A1))*60 calculates the decimal part (in minutes) by subtracting the whole number part from the original value and then multiplying by 60 to convert it from hours to minutes.
TEXT(...,"0") formats the decimal part as a whole number, ensuring it displays as minutes without decimal places.
The & operator concatenates the whole number part (hours), the text "hours and," and the formatted decimal part (minutes) into a single string.
So, if you enter 7.142857143 in cell A1, the formula will return "7 hours and 8 minutes," which is the equivalent of 7.14 hours.
The proposed steps/solutions are untested. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.

NikolinoDE

Platinum Contributor

Oct 09, 2023

lionheartva

To convert the decimal portion (the .14 in your case) into hours and minutes, you can use the following Excel formula:

Assuming that your decimal value is in cell A1, you can use the following formula to convert it into hours and minutes:

=INT(A1) & " hours and " & TEXT((A1-INT(A1))*60,"0") & " minutes"

Here's how it works:

INT(A1) extracts the whole number part, which represents hours.
(A1-INT(A1))*60 calculates the decimal part (in minutes) by subtracting the whole number part from the original value and then multiplying by 60 to convert it from hours to minutes.
TEXT(...,"0") formats the decimal part as a whole number, ensuring it displays as minutes without decimal places.
The & operator concatenates the whole number part (hours), the text "hours and," and the formatted decimal part (minutes) into a single string.

So, if you enter 7.142857143 in cell A1, the formula will return "7 hours and 8 minutes," which is the equivalent of 7.14 hours.

The proposed steps/solutions are untested. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

lionheartva

Copper Contributor

Oct 09, 2023

Thanks so much, that's worked great 🙂

NikolinoDE
Platinum Contributor
Oct 09, 2023
yw
- lionheartva
  Copper Contributor
  Oct 09, 2023
  How would I reverse this? So, 6 hrs 9 minutes into numbers?
  - NikolinoDE
    Platinum Contributor
    Oct 09, 2023
    lionheartva
    To convert a time duration in the format "X hours Y minutes" into a decimal number of hours in Excel, you can use the following formula. Assuming your time duration is in cell A1:
    =HOUR(A1) + MINUTE(A1) / 60
    Here is how it works:
    HOUR(A1) extracts the hours part from the time duration.
    MINUTE(A1) extracts the minutes part from the time duration.
    MINUTE(A1) / 60 converts the minutes into hours by dividing by 60 (since there are 60 minutes in an hour).
    HOUR(A1) + MINUTE(A1) / 60 adds the hours and the converted minutes to get the total duration in decimal hours.
    So, if you have "6 hours 9 minutes" in cell A1, the formula will return 6.15 as the decimal equivalent.