Common question and I've looked at a number of different answers in the
forum. None quite work for me though.
I can get the days part ok using INT, its the leftover hours and minutes
which is giving me grief" The following nearly gets the
=INT((Q5/7.5)*24)&" day "&TEXT(MOD(Q5,7.5),"hh"" hours"" mm"" minutes""")
However with 15:13 (hh:mm) in cell Q5, the result is 2 d 15h 13m rather than
2d 0h 13m

Any ideas?

--
Traa Dy Liooar

Jock

Use
=INT((Q5))&" day "&TEXT(Q5,"hh"" hours"" mm"" minutes""")

why are you dividing by 7.5? and multiplying by 24?

"Jock" wrote:

Common question and I've looked at a number of different answers in the
forum. None quite work for me though.
I can get the days part ok using INT, its the leftover hours and minutes
which is giving me grief" The following nearly gets the
=INT((Q5/7.5)*24)&" day "&TEXT(MOD(Q5,7.5),"hh"" hours"" mm"" minutes""")
However with 15:13 (hh:mm) in cell Q5, the result is 2 d 15h 13m rather than
2d 0h 13m

Any ideas?

--
Traa Dy Liooar

Jock

Because there's 7½ hours in a working day. Here anyway. Therefore 15 hours
and 13 minutes will equate to 2 (working) days and 13 minutes i.e 2d 0h 13m
--
Traa Dy Liooar

Jock


"Sheeloo" wrote:

Use
=INT((Q5))&" day "&TEXT(Q5,"hh"" hours"" mm"" minutes""")

why are you dividing by 7.5? and multiplying by 24?

"Jock" wrote:

Common question and I've looked at a number of different answers in the
forum. None quite work for me though.
I can get the days part ok using INT, its the leftover hours and minutes
which is giving me grief" The following nearly gets the
=INT((Q5/7.5)*24)&" day "&TEXT(MOD(Q5,7.5),"hh"" hours"" mm"" minutes""")
However with 15:13 (hh:mm) in cell Q5, the result is 2 d 15h 13m rather than
2d 0h 13m

Any ideas?

--
Traa Dy Liooar

Jock

Dear Jock,

Following formula will solve your problem

=INT(LEFT(TEXT(Q5,"hh:mm"),2)/7.5)&"d
"&INT((MOD(LEFT(TEXT(Q5,"hh:mm"),2),7.5)*60+MID(TE XT(Q5,"hh:mm"),4,2))/60)&"h
"&(((MOD(LEFT(TEXT(Q5,"hh:mm"),2),7.5)*60+MID(TEXT (Q5,"hh:mm"),4,2))/60)-INT((MOD(LEFT(TEXT(Q5,"hh:mm"),2),7.5)*60+MID(TEXT (Q5,"hh:mm"),4,2))/60))*60&"m "
--
HARSHAWARDHAN.S.SHASTRI

Pl do not forget to press "YES" button if post found useful.


"Jock" wrote:

Common question and I've looked at a number of different answers in the
forum. None quite work for me though.
I can get the days part ok using INT, its the leftover hours and minutes
which is giving me grief" The following nearly gets the
=INT((Q5/7.5)*24)&" day "&TEXT(MOD(Q5,7.5),"hh"" hours"" mm"" minutes""")
However with 15:13 (hh:mm) in cell Q5, the result is 2 d 15h 13m rather than
2d 0h 13m

Any ideas?

--
Traa Dy Liooar

Jock

Thanks for this. It works ok up until 24 hours is exceeded. I am using a
cumulative total of hours and there could be 200 plus hours in Q5.
Thanks for trying though
--
Traa Dy Liooar

Jock


"HARSHAWARDHAN. S .SHASTRI" wrote:

Dear Jock,

Following formula will solve your problem

=INT(LEFT(TEXT(Q5,"hh:mm"),2)/7.5)&"d
"&INT((MOD(LEFT(TEXT(Q5,"hh:mm"),2),7.5)*60+MID(TE XT(Q5,"hh:mm"),4,2))/60)&"h
"&(((MOD(LEFT(TEXT(Q5,"hh:mm"),2),7.5)*60+MID(TEXT (Q5,"hh:mm"),4,2))/60)-INT((MOD(LEFT(TEXT(Q5,"hh:mm"),2),7.5)*60+MID(TEXT (Q5,"hh:mm"),4,2))/60))*60&"m "
--
HARSHAWARDHAN.S.SHASTRI

Pl do not forget to press "YES" button if post found useful.


"Jock" wrote:

Common question and I've looked at a number of different answers in the
forum. None quite work for me though.
I can get the days part ok using INT, its the leftover hours and minutes
which is giving me grief" The following nearly gets the
=INT((Q5/7.5)*24)&" day "&TEXT(MOD(Q5,7.5),"hh"" hours"" mm"" minutes""")
However with 15:13 (hh:mm) in cell Q5, the result is 2 d 15h 13m rather than
2d 0h 13m

Any ideas?

--
Traa Dy Liooar

Jock

Your MOD function is looking for the excess over 7.5 days. You presumably
intended it to be the excess over 7.5 hours, so it would be MOD(Q5,7.5/24)
--
David Biddulph


"Jock" wrote in message
...
Common question and I've looked at a number of different answers in the
forum. None quite work for me though.
I can get the days part ok using INT, its the leftover hours and minutes
which is giving me grief" The following nearly gets the
=INT((Q5/7.5)*24)&" day "&TEXT(MOD(Q5,7.5),"hh"" hours"" mm"" minutes""")
However with 15:13 (hh:mm) in cell Q5, the result is 2 d 15h 13m rather
than
2d 0h 13m

Any ideas?

--
Traa Dy Liooar

Jock

In that case use
=INT((Q5/7.5)*24)&" day "&TEXT(MOD(Q5,7.5/24),"hh"" hours"" mm"" minutes""")

See David's post for the explanation...


"Jock" wrote:

Because there's 7½ hours in a working day. Here anyway. Therefore 15 hours
and 13 minutes will equate to 2 (working) days and 13 minutes i.e 2d 0h 13m
--
Traa Dy Liooar

Jock


"Sheeloo" wrote:

Use
=INT((Q5))&" day "&TEXT(Q5,"hh"" hours"" mm"" minutes""")

why are you dividing by 7.5? and multiplying by 24?

"Jock" wrote:

Common question and I've looked at a number of different answers in the
forum. None quite work for me though.
I can get the days part ok using INT, its the leftover hours and minutes
which is giving me grief" The following nearly gets the
=INT((Q5/7.5)*24)&" day "&TEXT(MOD(Q5,7.5),"hh"" hours"" mm"" minutes""")
However with 15:13 (hh:mm) in cell Q5, the result is 2 d 15h 13m rather than
2d 0h 13m

Any ideas?

--
Traa Dy Liooar

Jock