Hi HansV MVP,
As communicated by you, attaching file here.
Excel 2007 Issue
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel 2007 Issue
Welcome to Eileen's Lounge!
I don't see an attachment yet. See here for info on attaching a file to a post.
I don't see an attachment yet. See here for info on attaching a file to a post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 8
- Joined: 21 Mar 2016, 07:20
Re: Excel 2007 Issue
File upload is failing because of size;even zipped file is not working. If you can share mail id will send you email.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel 2007 Issue
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
Hans
-
- NewLounger
- Posts: 8
- Joined: 21 Mar 2016, 07:20
Re: Excel 2007 Issue
PFA
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel 2007 Issue
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.
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 8
- Joined: 21 Mar 2016, 07:20
Re: Excel 2007 Issue
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.
-
- NewLounger
- Posts: 8
- Joined: 21 Mar 2016, 07:20
Re: Excel 2007 Issue
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.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel 2007 Issue
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.
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
Hans
-
- NewLounger
- Posts: 8
- Joined: 21 Mar 2016, 07:20
Re: Excel 2007 Issue
Ohk.
Thank you for your quick replies.
Thank you for your quick replies.
-
- NewLounger
- Posts: 8
- Joined: 21 Mar 2016, 07:20
Re: Excel 2007 Issue
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
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
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel 2007 Issue
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.
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
Hans
-
- NewLounger
- Posts: 8
- Joined: 21 Mar 2016, 07:20
Re: Excel 2007 Issue
Thank you so much for explaining in detail !!
Now I'm convinced.
Now I'm convinced.