Indeed insightful ! Thanks Paul
Just to give a hands-on example of Paul's explanation on converting number
to date using String. Here is how it is, i just tried:
Cell(A1) -- "12/05/2009 " This is a date format
Cell(B1) -- "=TEXT(A1,"YYYYMMDD")" ,then in Cell B2, you get the string
presentation of the date, now you can export to any file (xml,text) and have
the date appearance as 20090512.
On Tue, May 12, 2009 at 3:09 PM, Aindril De <aind...@gmail.com> wrote:
> Excellent explaination Paul...
> Learnt something new today..
>
> Keep up the great work..
>
> Regards,
> Andy
>
> On Tue, May 12, 2009 at 7: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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
