Working with 8 hour shifts.

[GEM]

Let me give an example...

A1=2/13/2009 9:00 AM
A2=2/14/2009 10:00 AM

On a normal operation "A2-A1" this function would give me the answer 1 days
01:00. I'm looking to see this on 8 hours shifts, so the answer I'm looking
for is 3 days 1:00.

Someone helped me with this and gave me the following function,
=INT((A2-A1)/8*24)&" days "&TEXT(MOD((A2-A1)*24,8)/24,"h:mm") it worked
perfectly, it gave me the answer I was looking for "3 days 1:00", but this
function gives me the answer in a text format making it impossible to work
math operations, for example adding a series of cells. Is there anyway I can
get the answe 3 days 1:00 and be able to work with it??

 

[Niek Otten]

=INT((A2-A1)/8*24)+(MOD((A2-A1)*24,8)/24)

and use Pete_UKs advice from a few days back to Custom format the cell

 

[Pete_UK]

Use this formula:

=INT((A2-A1)/8*24) + MOD(MOD(A2-A1,1),1/3)

and use the custom format that I gave you yesterday, i.e. d" days
"hh:mm

Hope this helps.

Pete

 

[GEM]

Thank you!!!!!!!! I works!!!!

Use this formula:

=INT((A2-A1)/8*24) + MOD(MOD(A2-A1,1),1/3)

and use the custom format that I gave you yesterday, i.e. d" days
"hh:mm

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Thank you!!!!!!!! I works!!!!








- Show quoted text -

 

[Modeste]

Bonsour® GEM

Thank you!!!!!!!! I works!!!!

tssss... tssss...
Keep in mind that format : d" days "hh:mm
is not able to display no more that 31 days !!!!

either you can't use math opérations with "working day équivalent duration" like légal day duration !!!!!

for example : http://www.cijoint.fr/cjlink.php?file=cj200902/cij8LJLSzd.jpg

you must first compute normale duration for each period
Sum all normal duration
then apply "working day équivalent duration formula" and format