Different Datevalue for same date/time

[gnaga]

I am comparing two dates which are in the format of mm/dd/yyyy hh:mm. I have one date in cell a1 as 07/27/2011 07:00 and the other date in cell a2 as 07/27/2011 07:00.

But the value for first date is 40752.2916666667 and for the second date is 40752.2916666666

Can any one please explain me why is this difference?

 

[Taul]

try changing cell format to include seconds, I think that may be the reason.

 

[gnaga]

Reformatting to include seconds is also not helping. Any other clue?

 

[markmzz]

Try this:.

Note: the format in A1 and B1 is General and the format in A2 and B2 is mm/dd/yyyy h:mm:ss.000

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">40752.29166666670</td><td style="text-align: right;;">40752.29166666660</td><td style="text-align: right;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">07/28/2011 7:00:00.000</td><td style="text-align: right;;">07/28/2011 7:00:00.000</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #FFFF00;;">07/28/2011 7:00:00.0001</td><td style="background-color: #FFFF00;;">07/28/2011 7:00:00.000</td><td style="text-align: center;background-color: #FFFF00;;">type this in each cell A1 and B1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet8</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=A1=B1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=A1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=B1</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[gnaga]

I am not getting your idea. Do you think that it is a formatting issue?

 

[gnaga]

Guys,

Any idea? why this difference is coming?

 

[markmzz]

I am not getting your idea. Do you think that it is a formatting issue?

Gnaga,

Sorry, the data in cell B3 of my previous post was incomplete. Here it's ok:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">40752.29166666670</td><td style="text-align: right;;">40752.29166666660</td><td style="text-align: right;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">07/28/2011 7:00:00.000</td><td style="text-align: right;;">07/28/2011 7:00:00.000</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #FFFF00;;">07/28/2011 7:00:00.0001</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">07/28/2011 7:00:00.0000</td><td style="text-align: center;background-color: #FFFF00;;">type this in each cell A1 and B1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet8</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=A1=B1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=A1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=B1</td></tr></tbody></table></td></tr></table><br />
The problem with the data in cells A1/A2 and B1/B2 is that the smallest increment you can display using custom formats in Excel is a thousandth of a second (like the formats in A2 and B2). So, I can't display, in Excel, the format showing in cells A3/B3 (important: the data in the cells A3/B3 is text).

For more information, look at this:

http://excel.tips.net/T009198_Dealing_with_Small_Time_Values.html

I hope I have helped now.

Markmzz

 

[gnaga]

Thanks for the help. I understood the behaviour of timevalue from the link you provided and accordingly I sorted out my issue.

 

[markmzz]

Thanks for the help. I understood the behaviour of timevalue from the link you provided and accordingly I sorted out my issue.

Thanks for the feedback.

Markmzz