• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert Hours to Working days

M

MattGault

New Member
I realize this is probably very easy but I'm stumped and can't figure it out. In excel, I have hours in a h:mm format. I can easily change formats to dd:h:mm , but can't figure out the best way to convert this to working days (8 hour) i.e. [wd:h:mm].

Any help would be appreciated.
 
Hi Matt ,

I think you need to give at least one or two examples of what results you expect :

1. If the actual time is 11:20:00 , what should the display be ?

2. If the actual time is 22:35:00 , what should the display be ?

Narayan
 
Hi Narayan,

Yes I should have explained this further:

I am calculation duration in hours and minutes (h:mm). So I may have a total duration of 160:00. Would like to convert it to working days, hours, minutes. Duration is 160:00, would like it to read 20:00:00.
 
Hi Matt,

I think, normal A1/8, will also work, if your data is actually Time Value.

or may be.. =TEXT(A1/8,"[hh]:mm:ss")


Is this acceptable..!!
 
Hi Matt ,

It is not advisable to use a standard format to mean something else.

There was a thread earlier where the question was how to show the number 5 as 5 % ; normally , Excel automatically converts data entry of 5% to 0.05

When the standard format for hours , minutes and seconds is hh:mm:ss , to use the same format to represent working days , is not advisable ; also , I do not know why you wish to retain this format. If you wish to display the number of working days , why not let the user know that what they are seeing is the number of working days ?

Use a formula , and convert 160:00:00 to display 20 working days.

Narayan
 
Hi, MattGault!
Tried with this:
=ENTERO(A1*24/8)+RESIDUO(A1;1/3) -----> in english: =INT(A1*24/8)+MOD(A1,1/3)
and formatted as wd:h:mm?
Regards!
 
Hello!

Thank you everyone for your generous replies as always.

SirJB7 - That's the formula I was after and it worked great! My gratitude.
 
Hi, MattGault!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top