Excel date/Time calc over several days

[disturbedone]

Hi,

I've seen various posts on this and also various websites with various solutions to calculating time between two date/time values that go over midnight. The problem is that all of these solutions only deal with it going from one day to the next not, as in my case, over 2/3/5/10 days.

There are ways to tell Excel to see if the first time is greater than the second and if so add 1 day to the value but this doesn't work if it spans more than 1 day.

eg Date/Time 1 = 9/9/09 10:00 AM
Date/Time 2 = 10/10/09 11:00 PM
The result should be 31 days and 1 hour. But using various methods all I can get Excel to show is 13 hours.

Is there an easy formula for this? I've tried =A2-A1, =A2+(A2>A1)-A1 and a few others.

Cheers

 

[SkipVought]

Hi,

FYI, faq68-5827

This is not a problem IF your data is entered correctly, using BOTH DATE & TIME. In fact, it's just a simple mathematical difference. Then just FORMAT to suit, to DISPLAY hours greater than 24 hrs, in hh:mm format, for instance.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[disturbedone]

Thanks. But....it doesn't answer the question. The link doesn't answer the question, it just says that date/time in MS apps is a number.

Date/Time appears on the screen as follows
9/9/09 10:00
Viewing the cell it shows in the formula bar as
9/9/09 10:00:00 AM
Selecting Format Cells/Number it is set as
Custom d/mm/yyyy h:mm

New cell formula=A2-A1 (Format=General) gives value of 31.04166667
Format new cell to be 'Custom h:mm' gives a value of 1:00 when it should give the value of 13:00. Or is there a different format it should be in?

Adding 1 day to the A2 cell makes the value of the new cell 32.4166667 (adding 2 days is 33.4166667 and so on).

So it appears to be the formatting of the cell that is the issue. So the question is, what is the correct format for the cell to display the time difference correctly.

ps I'm using Excel 2007.

 

[SkipVought]

The DURATION of

31.04166667

Days

custom time...
[tt]
[hh]:mm:ss
[/tt]
will display hours greater than 24, as
[tt]
745:00:00
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[disturbedone]

Thx but still not getting an answer.

745:00:00 is what? It can't be 745hrs 0mins 0s. It's not 745mins because that's 12hrs 25mins and the times I'm testing with is 13hrs.

So where, in this instance, does 745:00:00 come from?

The question is....how can I, or anyone, get it to display the number of hours, minutes, seconds between two date/times?? Or even preferably, if using larger gaps, years/months/days/hours/minutes/seconds??

 

[SkipVought]

OK, if your two date/time values in A1 & A2 are
[tt]
9/9/2009 10:00
10/10/2009 23:00
[/tt]
the duration in d hh:mm:ss is
[tt]
=INT(A2-A1)&"-"&TEXT(A2-A1-INT(A2-A1),"hh:mm:ss")
[/tt]
as TEXT, since you really cannot format DURATION in days hrs, min sec.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[disturbedone]

We have a winner!

I'm using this to calculate the time between opening a case and the resolution. I altered the formula to include an IF statement so there's no resolution date then it displays some text and if it is resolved then gives the time it took.

=IF(A2=0,"no resolution",INT(A2-A1)&" day(s) "&TEXT(A2-A1-INT(A2-A1),"hh:mm:ss"))

An example of the resulting viewable field is
"1 day(s) 14:34:00"
meaning that the difference in the times was 1 day, 14hrs, 34mins and 00secs. It sounds like it would be too difficult, of not impossible, to get it to to years/months/days etc but this is good enough.

Thx

 

[xlhelp]

It's not that difficult. Just problematic because we are so used to base 10 (decimal) system. Once you get familiar with systems that have base 8, base 16 and in our case base 24, base 60 etc, the fog clears.

Simple way of looking at it is that difference between two (date and time) serial numbers yields number of days.

A man has only two choices: He can be right or he can be happy.

 

[SkipVought]

The issue with MONTH and YEAR is their inexactness. Days, however ARE exact.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

If the time period is shorter than 32 days and positive, use custom formatting only: d "day(s)" hh:mm.
You will have exact duration in days, with fractional part behind. (Or, more precisely, a date representing elapsed time since beginning of excel calendar.)

combo