Excel 2007 : Finding the difference in time

Hi and welcome to the board.
To make sure, can you post a small example of your data?

Originally Posted by arthurbr

Hi and welcome to the board.
To make sure, can you post a small example of your data?

Thanks for the welcome.

I'm not sure what you mean by a small example but I pulled out some data points and attached it in a separate excel file.
sample_timedifference.xlsx

I came to a partial solution by how I formatted the End Date Window into an instance through the following formula

=TEXT(LEFT(A15,LEN(A15)-8),"dd/mm/yyyy h:mm")

However, the results show that the month/day is being read wrong, BUT I'm getting the correct time difference result (for once).

However, a new issue appears and in some cases, the time difference will show a "#VALUE" error.

I'm quite confused by the process of what I've done and how it works for some cases but not all of them.

Any help would be greatly appreciated.

I just realized that this was more of a general excel question; Is there any chance a moderator could move this thread to "Excel General"?

Originally Posted by DonkeyOte

If you go to Regional Settings in your Windows Control Panel can you tell us which Region is active ?

The regional setting impacts the way date strings are interpreted by Excel - we need to know which it is - sounds like it might be US rather than Canada.

Everything is set to US (Language for non-Unicode programs,Standards and formats, and even Location).

Hope that helps.

Thanks guys, the formulas almost work perfectly. But I'm having difficulty with two particular records (out of 100s of successful ones) and I'm still not sure why.

I tried both formulas from DonkeyOte and Daddylongleg but when the formula comes across these two records: Daddylongleg's formula will generate a time difference of "#VALUE" and DonkyOte's formula's generated value will be incorrect.

I've attached a file demonstrating the formula's working for another record but not working for the two troublesome ones.

sample_timedifference v2.xlsx

Thanks in advance!

Hi DonkeyOte,

Using that modification to DLL's formula will fix the "#VALUE" issue but I'm also unsure about the actual results
for example if I'm comparing end date 9/11/2009 with start date 6/11/2009; I should have a time difference of approximately 3 days. However, in these two records I'm getting something close to 150 days. It would appear that it's reading these two records start date as MM/DD/YYYY which was kind of the issue in the first place.

This data set has been giving me an odd amount of problems for just formatting.
Since it's only two records, it's something I can probably manually calculate but it still bothers me just a bit (in cases where I might run across the same issue).

Any ideas?

Originally Posted by DonkeyOte

The date values in C20:C23 are all Jun 2009 rather than Nov 2009 which you're implying
(the values in C15:C16 being Oct 2009)

I just checked the End Date Range field again and I think I realize the issue.
The formula to change the End Date Range into a the End Date Instance is only for ranges that are within the the same date.

For example: 8/11/2009 08:00 - 18:00 (length: 23)

However, for these two problem records they have End Date Ranges that span multiple dates
IE: 9/11/2009 04:00 - 12/11/2009 22:00.

I think the best way around this would be to make a condition such as
=if(len(A1)=23, DLL modified formula for single date ranges, new formula for the multidate range).

Originally Posted by DonkeyOte

No, that's not the issue.
The multiple date strings were the cause of the #VALUE! errors you reported earlier for which a fix was provided (post #11)

The cause of the "unexpected" results is simply that your dates in C20:C23 are in fact June 2009 and not November 2009 as it would seem you believe them to be based on expected results you outline.
This was the point made in post # 13

To confirm this apply: =MONTH(C20) and you should find the value returned is 6 as opposed to 11

Conversely the same formula is applied to C15 will return 10

The confusion stems from the fact different number formats have been applied to the date values in Column C - some are displaying as dd/mm and others as mm/dd
The results generated by the formula are correct based on the date values in Column C and the strings in A.

edit: the above is all based on sample file provided in post # 10

I think I have made two big mistakes:
1) The sample file posted in #10 was incorrectly made (formatting of start date)
2) The correct solution in post #11 was applied to the incorrect sample file leading to more confusion on my part.

Good news is I think all is well!
Thanks for all your help DonkeyOte; (you were several steps ahead of me).

Regards,
Peter