Converting hours to days in Excel

Hello,

If I understood correctly, the solution was provided by Gbinforme, whom I greet.
If in a cell, for example A1, we have 48:00 and in B1 7:15, you just need to write in a cell
=A1/B1
or directly input in the formula
="48:00"/"7:15"
and set the formula to a custom format (Format/Cell/Number/Custom and enter this format which was given by Gbinforme

j hh:mm:ss

and the displayed result will be 6 14:53:48

With this format j "j" hh:mm:ss we will have 6 j 14:53:48

or even j "day(s)" hh:mm:ss to get 6 day(s) 14:53:48

we could also play with hours and minutes to display, for example

6 day(s) 14 hour(s) 53 minute(s) 48 second(s)

and with more complex formulas to manage singular and plural

Best,
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

Hello
I will add, in addition to the format, a small formula modification because we get 6d 14h53m48s, whereas 14h53m and 48 seconds correspond to 2 days and something, which seems wrong to me:

Here is my modification:
=FLOOR(A1/B1;0) +(C1-FLOOR(C1;0))*B1

and we get 6 days 4h and 30m, which seems more accurate considering the initial request.

Good luck

jeje

Hello everyone

I don't understand what you don't like about the first response:

cell format: "days" h:mm:ss given by gbinform
calculation in the cell: =FLOOR(A1/B1;0) +(C1-FLOOR(C1;0))*B1

because 1 day = 7h15 and not 24h as it is a working day.

which gives 6 days 4 hours and 30 minutes.

later

jéjé

Hello everyone,
In this last message#10 everything is good except the formula in C1 which gives a circular reference.
It should be written in C1=FLOOR(A1/B1,0)+A1-(FLOOR(A1/B1,0))*B1
For the formats of this cell C1 Mike-31 has said everything in message #6.

The question is not a question of conversion.
The solution lies in choosing the right format:

1 gives 24:00:00 with the format [hh]:mm:ss.

Then, you need to understand how Excel handles times and dates. I refer you to an explanation that I have tried to make clear on the blog:
http://ge-pe-se.blogspot.com/2008/10/dates-et-heures-dans-les-tableurs.html

Thank you all for your quick responses.
I followed Jéjé and Mike's method, but the result shows 00:00:00!
Thanks anyway and have a good weekend.

In A1 enter 48:00 and in B1 enter 7:15.

This is what I did:

in A1: 48:00
in B1: 7:15
in C1 the calculation formula =ROUNDDOWN(A1/B1;0) +(C1-ROUNDDOWN(C1;0))*B1
and in cell format: "days"h:mm:ss
and my result displayed in cell C1 is: 00:00:00
!!!!!!!!

Re,

Still for the number of hours ex. 48:00 in A1 and the weekly duration 7:15 in B1
in C1 the formula will be

=FLOOR(A1/B1,0)+A1-B1*FLOOR(A1/B1,0)

with a custom format that can be modified
j "day(s) "hh:mm the result will be 6 day(s) 04:30

if you need to increment this formula downwards and use the weekly duration reference of 7:15 in B1 the formula will be

=FLOOR(A1/$B$1,0)+A1-$B$1*FLOOR(A1/$B$1,0)

A+
Mike-31

A failure period is a perfect moment to sow the seeds of knowledge.

Hello,

I'm bringing this post back up which isn't very recent but perfectly matches what I'm looking for (last message from Mike-31 with the following formula: =FLOOR(A1/B1;0)+A1-B1*FLOOR(A1/B1;0)

I would like to complete an Excel table with the same logic by adding a cell that converts the number of hours into weeks (based on 39h00 and/or 5 days) and a second cell that converts the number of hours into months (based on 156h00).

Can you please help me?

Thanks in advance.

I think you should create an additional cell to put the number of hours you have in minutes, knowing that your 7h15 day is 435 minutes, then divide the number of minutes by that figure to see the number of days.

Hello

In your result cell, you put the format:
j" days " h:mm:ss

instead of [h]:mm:ss
--

Always zen