Counting days and hours

[roddie]

Hi,

I have a questions about counting days and hours.

Imagine that i have an item entering my warehouse on 22/05/2008 at 21h35 and leaving on the 25/05/2008 at 5h42.

A1= 22/05/2008 and B1= 21h35
A2= 25/05/2008 and B2= 5h42

The goal is to count the full 24 hours day and the remaining hours.

For the example given i can say that on day 22 the item only says 2 hours and 25 minutes, on day 23 it stays 24 hours and on day 24 another 24 hours, on day 25 the item leaves at 5h42, so it only stays those 5h42.

So we have 2 full 24 hour days and 2h25 plus the 5h42, the the item were stored 2 days and 8 hours and 7 minutes.

The problem is when the item arrives at (example) 22/05/2008 2h00 and leaves at 25/05/2008 23h00 on another day. Lets say that on the first day the item is 22 hours stored, plus the 2 full 24 hour day and another 23 hours.
So it is 2 days plus the 45 hours, that is 2 days plus 1 day and the remaining 21 hours.

The problem is that i dont know how to add to this 2 full days the 1 day and 21 hours

I hope that my explanation is clear enough.

Best regards,

Rodrigo Catarino

[arthurbr]

Depending on what you want as result try

=a2+b2-a1-b1

and format as dd:mm:hh
If you want a decimal result, format as number

Cheers

[Ron Coderre]

With
A1: 22-May-2008
B1: 2:00
A2: 25-May-2008
B2: 23:00

If the storage time will ALWAYS be less than 30 days,
try this:

=TEXT((A2+B2)-(A1+B1),"d\d hh\h:mm\m")

Otherwise, if the storage time may exceed 30 days,
try this formula:

C1: =TEXT(INT((A2+B2)-(A1+B1)),"0\d ")&TEXT((A2+B2)-(A1+B1),"hh\h:mm\m")

Using the above values, both formulas return: 3d 21h:00m

But if A2 is 30-Sep-2008,
the first formula returns: 10d 21h:00m
while the second returns: 131d 21h:00m

Is that something you can work with?

[roddie]

Hi,

Thanks in advance for your help.

I still have a problem...

For all three formulas i had an error.

Ron: I had error here <"0\d> and <"d\d>

I have access to excel 2000 and 2007, the formulas havent worked on both versions.

Sorry...

Can you please help me out?



Best regards,

Rodrigo

[Ron Coderre]

Question:

Does the formula work if you copy it from the screen onto your worksheet?

[roddie]

Hi Ron,

Thats what i have done...
I have just copied the formula to the worksheet.


Best regards,

Rodrigo Catarino

[Ron Coderre]

I'm a bit puzzled that you don't get an error at the "\h" section. Perhaps it's only because the other error occurs first.

According to MS Help:
"To display both text and numbers in a cell, enclose the text characters in double quotation marks (" ") or precede a single character with a backslash (\). "

Consequently, the format code I posted should work.
Let's see if we can figure out why it doesn't.

Can you post a simple example in a zipped workbook?

[nandu141]

the formula will definitely work. If the cell names existed in the formula should contain apporopriate values....