# formula hour-minute

1. ## formula hour-minute

Hello everyone this formula:
transform hours/cents in hours/minutes

it does not work properly.
If the hours are greater than 24, it is not correct
I hope I explained.
I am attaching the sample files.
max_max

2. ## Re: formula hour-minute

The formula

=ORA(1/24*C8)+MINUTO(1/24*C8)/100+SECONDO(1/24*C8)/10000

=HOUR(1/24*C8)+MINUTE(1/24*C8)/100+SECOND(1/24*C8)/10000

max_max.xls

3. ## Re: formula hour-minute

Try

=TEXT(A2/24,"[hh]:mm:ss")

4. ## Re: formula hour-minute

I would suggest a review of how Excel stores dates and times: http://www.cpearson.com/Excel/datetime.htm#SerialDates

Since time is simply "fraction of a day" convert the decimal hours to fraction of a day: =A2/24
Format as elapsed time (not "time of day"). Format code like [h]:m:s https://support.office.com/en-us/art...2-09fab54be7f4

5. ## Re: formula hour-minute

Hello ace_xl
your formula works, but it is not the correct:
=SUM(C2:C7)
perhaps not accept the format TEXT.
max_max

max_max_2.xls

6. ## Re: formula hour-minute

=SUM(C2:C7) perhaps not accept the format TEXT.
Correct, the SUM() function ignores text arguments, including "numbers stored as text". Ace_XL's formula returns a text string, so the sum function ignores results of the TEXT() function.

Did you try my solution? Mine does the same thing, except it does not convert the values to a text string. It relies on number formatting to provide the sexagesimal notation we are used to.

7. ## Re: formula hour-minute

Hello MrShorty.
I tried it and it works as you say.

=A3/24
size > Custom > [hh]:mm:ss

Thank you.
A greeting.
max_max

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1