Counting days and hours

**roddie** · 05-28-2008, 08:00 AM

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** · 05-28-2008, 08:31 AM

Depending on what you want as result try

Please Login or Register  to view this content.

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

Cheers

**Ron Coderre** · 05-28-2008, 08:45 AM

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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** · 05-28-2008, 09:55 AM

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** · 05-28-2008, 10:44 AM

Question:

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

**roddie** · 05-28-2008, 11:00 AM

Hi Ron,

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

Best regards,

Rodrigo Catarino

**Ron Coderre** · 05-28-2008, 12:33 PM

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?

**roddie** · 05-29-2008, 04:53 AM

Hi,

Here goes the file...

Thanks for your help.

Best regards,

Rodrigo

**Ron Coderre** · 05-29-2008, 07:31 AM

Hi, Rodrigo

After opening the file you posted, I:
-selected each formula cell
-pressed [F2] to edit it
-pressed [ENTER] to commit
...and the correct result displayed without error.

Here a some of my thoughts:
By any chance are you using a non-english version of Excel
where a different character is used to flag text in a number format?
If you use Excel Help and search for "custom number format", find
the section on Text and Spacing and find the comment that identifies
the character to preceed text in the format. Is it the backslash (\)?

**roddie** · 05-29-2008, 07:43 AM

Hi,

In fact i am using a Portuguese version of the excel.

I will see what i can find with the help

Best regards,

Rodrigo

**roddie** · 05-29-2008, 11:30 AM

Hi,

I have installed the english version of the excel 2000 and i still get the same error.

Do you have any idea why is this happening?

Best regards,

Rodrigo

**Ron Coderre** · 05-29-2008, 12:26 PM

Sorry, I can't figure out why the "\" code isn't working for you.

However, this formula works around that issue:

Please Login or Register  to view this content.

Does that help?

**roddie** · 05-29-2008, 12:35 PM

Hi Ron,

I still have an error. The error is at ,"0"
I have tryed this on the portuguese and english version of the excel.

I dont know what am i doing wrong.

I will try not to copy/paste but to type the entire formula "step by step".

Best regards,

Rodrigo

**Ron Coderre** · 05-29-2008, 12:56 PM

See if this helps....

I attached a zipped Excel workbook that
contains the two versions of the formula.

Do either of the formulas work on your computer?