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