Yes, back to the original post.
the VALUE in the cell is NOT A DATE!
It is a NUMBER.
The FORMAT of the cell is DISPLAY the number as if it were a date!
I don't export to XML, so I'm not sure if there are settings that would handle
the date conversion.
what you're doing to convert the number to a date STRING should fix it though.
paul
________________________________
From: zheng yu <vincent2...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Tuesday, May 12, 2009 8:54:55 AM
Subject: $$Excel-Macros$$ Re: Concatenating Problem
Precise! Very useful !
when I export a Date (appears as a Date format) to XMl..why it appears as a
number 39919.375.....any idea on that?
On Tue, May 12, 2009 at 2:15 PM, Paul Schreiner <schreiner_p...@att.net> wrote:
First, I want to remind you that the "date/time" isn't being stored as a "date".
Excel stores it as a NUMBER ("4/16/2009 9:00 AM" is stored as: 39919.375)
But, you're using a cell format of: "m/d/yy h:mm AM/PM" (or similar) to DISPLAY
the number as a date/time.
Excel then checks the fraction of a day (.375) and determines that it is less
than .5, so therefore
the designation of AM is used.
When you're concatenating the 'time zone' to the date, you're creating a text
string that no longer
retains the date "number" (therefore, future date manipulations will not be
possible)
That being said, what YOU need to do is duplicate what Excel does.
find the time portion of the date/time by determining the decimal(fractional)
portion
of the date number.
I chose to find the INTEGER portion of the number and subract it from the
number (A1 - INT(A1))
then, check to see if this is the first half of the day, or the second half:
(A1 - INT(A1)) < .5
If it is, then display "AM", if not, display "PM".
or: =IF(A1-INT(A1)<0.5,"AM","PM")
the result is:
=CONCATENATE(MONTH(A1),"/",
DAY(A1),"/",
YEAR(A1)," ",
HOUR(A1),":",
MINUTE(A1),
SECOND(A1)," ",
IF(A1-INT(A1)<0.5,"AM","PM")," ",
B1)
good luck,
Paul
________________________________
From: "jamadagnira...@gmail.com" <jamadagnira...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Cc: jamadagnira...@gmail.com
Sent: Tuesday, May 12, 2009 3:57:44 AM
Subject: $$Excel-Macros$$ Concatenating Problem
Hello,
I have a problem in displaying AM or PM, when i try to concatenate to
cells. Below is the reference -
Column A Column B Column C
4/16/2009 9:00 AM CDT 4/16/2009 9:00 CDT
You see, after i concatenate Column A and Column B, in Column C, i get
the above value. If you notice, i am not getting "AM" text in Column
C. Below is the formula i used -
=CONCATENATE(MONTH(A1),"/",DAY(A1),"/",YEAR(A1)," ",HOUR(A1),":",MINUTE
(A1),SECOND(A1)," ",(B1))
Please can anyone give me the formula to concatenate, so that i get AM
or PM accordingly in Column C. Please help.
Regards,
Raghu J.
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
