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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
