Converting hours to days in Excel
Jauly
-
JvDo Posted messages 1924 Registration date Status Membre Last intervention -
JvDo Posted messages 1924 Registration date Status Membre Last intervention -
Configuration: Windows 2003 / Internet Explorer 7.0
Hello,
I would like to convert a number of hours into days knowing that a day = 7h15,
on the Excel spreadsheet
I do 48:00/7:15. I get 158:53:48!
When I should get 6 days and xxx minutes
Is there a trick?
Thank you in advance
Hello,
I would like to convert a number of hours into days knowing that a day = 7h15,
on the Excel spreadsheet
I do 48:00/7:15. I get 158:53:48!
When I should get 6 days and xxx minutes
Is there a trick?
Thank you in advance
12 réponses
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.
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
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é
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.
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
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.
I followed Jéjé and Mike's method, but the result shows 00:00:00!
Thanks anyway and have a good weekend.
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
!!!!!!!!
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.
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'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.
