Excel 2007 Issue

tanujajain
NewLounger
Posts: 8
Joined: 21 Mar 2016, 07:20

Excel 2007 Issue

Post by tanujajain »

Hi HansV MVP,

As communicated by you, attaching file here.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel 2007 Issue

Post by HansV »

Welcome to Eileen's Lounge!

I don't see an attachment yet. See here for info on attaching a file to a post.
Best wishes,
Hans

tanujajain
NewLounger
Posts: 8
Joined: 21 Mar 2016, 07:20

Re: Excel 2007 Issue

Post by tanujajain »

File upload is failing because of size;even zipped file is not working. If you can share mail id will send you email.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel 2007 Issue

Post by HansV »

Can you create a copy of the workbook with only the information necessary for the question? That should be smaller than 250 KB.
Best wishes,
Hans

tanujajain
NewLounger
Posts: 8
Joined: 21 Mar 2016, 07:20

Re: Excel 2007 Issue

Post by tanujajain »

PFA
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel 2007 Issue

Post by HansV »

The formulas in your workbook don't work for me since I have different regional settings.

I have changed the formulas to the ones I proposed in your thread in Microsoft Community.
As far as I can tell it produces the desired results.
ExcelCalculations.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

tanujajain
NewLounger
Posts: 8
Joined: 21 Mar 2016, 07:20

Re: Excel 2007 Issue

Post by tanujajain »

Excel.png
Yeah, your solution works fine but as i said the formulas which i have been using should also work.
I think this is a bug in Excel 2007.
PFA screenshots of my excel calculations.
You do not have the required permissions to view the files attached to this post.

tanujajain
NewLounger
Posts: 8
Joined: 21 Mar 2016, 07:20

Re: Excel 2007 Issue

Post by tanujajain »

Attached screenshot is of correct results in SECONDS using TEXT function.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel 2007 Issue

Post by HansV »

As you can see, the date in B2 is displayed as 16/02/29; this is interpreted as 16 February 2029.
The date in D2 is displayed as 16/02/28; this is interpreted as 16 February 2028.
The difference between these dates is 1 year, or more than 31 million seconds...

The formula that I proposed does not have this problem, and moreover does not rely on a specific regional setting.
Best wishes,
Hans

tanujajain
NewLounger
Posts: 8
Joined: 21 Mar 2016, 07:20

Re: Excel 2007 Issue

Post by tanujajain »

Ohk.
Thank you for your quick replies.

tanujajain
NewLounger
Posts: 8
Joined: 21 Mar 2016, 07:20

Re: Excel 2007 Issue

Post by tanujajain »

Hi HansV,

As you said in your last reply that the difference between these dates is 1 year that's why result in seconds is coming like that.
If it is so then how come the difference in hh:mm:ss is correct.

Sorry to trouble you again but I'm not convinced.


Thanks,
Tanuja

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel 2007 Issue

Post by HansV »

Your formulas get the date wrong, but the time is correct. So the hh:mm:ss part is OK.

To understand why, it is useful to know that date and time formats are only ways of displaying a value. Regardless of the display format, Excel stores dates and times as numbers, with 1 day = 24 hours as unit.

A date is (roughly) the number of days since 31 December 1899. So 1 January 1900 is stored as 1, 2 January 1900 is stored as 2, etc.
A time is a fraction of a day, for example 6:00 in the morning is stored as 6/24 = 0.25.

Your formula returns a difference of 365.00398, that it 365 days (1 year) plus 0.0398 days = 00:05:44.
When you format the difference as hh:mm:ss, the 365 days are ignored, and you see only the time part 00:05:44.
But when you multiply the difference with the number of seconds in a day, the 365 days are included in the result, so you get a very large number.
Best wishes,
Hans

tanujajain
NewLounger
Posts: 8
Joined: 21 Mar 2016, 07:20

Re: Excel 2007 Issue

Post by tanujajain »

Thank you so much for explaining in detail !!
Now I'm convinced.