Convert hours to days

[Guest]

I am calculating difference in dates in 24 hour format to give me a elapsed
time this has given me a large number of hours:min:seconds. Can anyone
convert this to a format of days, hours, minutes?

 

[Guest]

Format > Cells... > Number > Custom
dd:hh:mm

 

[David Biddulph]

If you're not going to go beyond 30 days or so, then you could use the
format:
d "days" hh:mm

If you want to go further then you could use the formula:
=INT(A1)&" days "&TEXT(A1,"hh:mm")

 

[Guest]

The calculation was large, 1395 hours and the second solution did work.
Thanks.

 

[Junior Siqueira]

Thank You, David.

You're "THE MAN"... Very, very happy with solution.

Regards

"David Biddulph" escreveu:

 

[Resi]

Hi
thanks everyone for your help which I tried to sort out, but still having a
problem.
when I apply the formula it says I only worked 4 day in the whole of January.
the cell I am referring to says 4:10 (hh:mm) - this is the sum of my daily
entries (start time -end time)
when I apply the formula:
=INT(A1)&" days "&TEXT(A1,"hh:mm") the answer is 4 days 4:10

is there anyway for the hours field to give me total hours worked not just
the number of hours over 24hrs periods? i.e. 28:10 (if that the case)

the decimal conversion of the 4:10 is: 100.17

your help would be fantastic!!
thanks
Resi

 

[David Biddulph]

So you are now saying that you don't want the days, hours, and minutes that
you originally asked for, but instead you want hours and minutes?

In that case, format as [h]:mm

 

[Resi]

no, what I would like is for the hh:mm to be converted to days:hh:mm
but at moment when I apply the formula the days are wrong.
actually it might be right, because is 4 days at 24 hrs each...
sorry the penny just dropped!

so rephrasing my query... can I have the hh:mm to show all the hours when
more than 24hrs?
is I worked 28 hrs 15 min on a project now it shows 4:15 and I like to show
it as 28:15

hope you can help
Resi

So you are now saying that you don't want the days, hours, and minutes that
you originally asked for, but instead you want hours and minutes?

In that case, format as [h]:mm
--
David Biddulph

Hi
thanks everyone for your help which I tried to sort out, but still having
a
problem.
when I apply the formula it says I only worked 4 day in the whole of
January.
the cell I am referring to says 4:10 (hh:mm) - this is the sum of my daily
entries (start time -end time)
when I apply the formula:
=INT(A1)&" days "&TEXT(A1,"hh:mm") the answer is 4 days 4:10

is there anyway for the hours field to give me total hours worked not just
the number of hours over 24hrs periods? i.e. 28:10 (if that the case)

the decimal conversion of the 4:10 is: 100.17

your help would be fantastic!!
thanks
Resi

 

[Resi]

Thanks David! I just figure out what to do!
many many thanks
resi

no, what I would like is for the hh:mm to be converted to days:hh:mm
but at moment when I apply the formula the days are wrong.
actually it might be right, because is 4 days at 24 hrs each...
sorry the penny just dropped!

so rephrasing my query... can I have the hh:mm to show all the hours when
more than 24hrs?
is I worked 28 hrs 15 min on a project now it shows 4:15 and I like to show
it as 28:15

hope you can help
Resi

So you are now saying that you don't want the days, hours, and minutes that
you originally asked for, but instead you want hours and minutes?

In that case, format as [h]:mm
--
David Biddulph

Hi
thanks everyone for your help which I tried to sort out, but still having
a
problem.
when I apply the formula it says I only worked 4 day in the whole of
January.
the cell I am referring to says 4:10 (hh:mm) - this is the sum of my daily
entries (start time -end time)
when I apply the formula:
=INT(A1)&" days "&TEXT(A1,"hh:mm") the answer is 4 days 4:10

is there anyway for the hours field to give me total hours worked not just
the number of hours over 24hrs periods? i.e. 28:10 (if that the case)

the decimal conversion of the 4:10 is: 100.17

your help would be fantastic!!
thanks
Resi


:

Thank You, David.

You're "THE MAN"... Very, very happy with solution.

Regards

"David Biddulph" escreveu:

If you're not going to go beyond 30 days or so, then you could use the
format:
d "days" hh:mm

If you want to go further then you could use the formula:
=INT(A1)&" days "&TEXT(A1,"hh:mm")
--
David Biddulph

I am calculating difference in dates in 24 hour format to give me a
elapsed
time this has given me a large number of hours:min:seconds. Can
anyone
convert this to a format of days, hours, minutes?