Concatenate including a date so that the date appears as a date

When I try to join the contents of several cells, one of which is a date and 
time obtained from =now(), the date and time part appears as a number.  How 
can I make it appear as a date and time?  I have tried changing the cell 
formats - probably there is a combination that works but I haven't found it.  
I also tried copying the original date cell to another cell and changing the 
latter cell's format and concatenating using that latter cell, but still no 
success.
0
Utf
1/6/2010 5:09:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
972 Views

Similar Articles

[PageSpeed] 58

Use the TEXT function. For example, =TEXT(NOW(),"mmmm d, yyyy") would give 
January 6, 2010
"ddd mm-dd-yy" would give Wed 01-06-10,
etc.

"Zembu" wrote:

> When I try to join the contents of several cells, one of which is a date and 
> time obtained from =now(), the date and time part appears as a number.  How 
> can I make it appear as a date and time?  I have tried changing the cell 
> formats - probably there is a combination that works but I haven't found it.  
> I also tried copying the original date cell to another cell and changing the 
> latter cell's format and concatenating using that latter cell, but still no 
> success.
0
Utf
1/6/2010 5:22:01 PM
Try this...

A1 = Today's date is

=A1&" "&TEXT(NOW(),"m/d/yyyy h:mm AM/PM")

Will return something like:

Today's date is 1/6/2010 1:08 PM

-- 
Biff
Microsoft Excel MVP


"Zembu" <Zembu@discussions.microsoft.com> wrote in message 
news:152638B7-5B6E-48F7-9332-5CE2EDA7A058@microsoft.com...
> When I try to join the contents of several cells, one of which is a date 
> and
> time obtained from =now(), the date and time part appears as a number. 
> How
> can I make it appear as a date and time?  I have tried changing the cell
> formats - probably there is a combination that works but I haven't found 
> it.
> I also tried copying the original date cell to another cell and changing 
> the
> latter cell's format and concatenating using that latter cell, but still 
> no
> success. 


0
T
1/6/2010 6:09:02 PM
Reply:

Similar Artilces:

Copying a formulae down a column that includes an INDIRECT
For example, in C1 I have a long formulae that includes multiple INDIRECT references to B1 which is where I input a data figure - column B has data that is dragged and dropped between cells up and down that column (hence the reason for including the indirect) column C provides the answer. After setting up a formulae in C1 how can I copy this down column C and make the indirect move with it - currently the indirect always stays refering to cell B1 ???? =indirect("B"&row()) if that's the formula in C1. A.Webb wrote: > > For example, in C1 I have a long formulae ...

When concatenating concatenates don't concatenate...
Hi List, Can anyone help? When concatenating already-concatenated cells, th result displays perfectly well in the Excel spreadsheet, but truncate when the cell is pasted into a .txt file. It doesn't seem to be due t Data Validation limits (having said that, selecting the entir worksheet and doing Alt > Data > Validation > Validation criteria Allow = Any value" did seem to solve the problem once, but only to com back next time round). The truncation occurs sometimes after 8 or 1 chars, and sometimes after 20 or so, always at the same spot. If I cop the cell into a fresh Excel...

Counting dates
I am trying to count the number of dates in a column that appear b month and year. For instance, I some that are 11/23/2003 and 11/24/200 and 11/21/2002. How can I makew a formula that will count the instance of 11/2003 -- Message posted from http://www.ExcelForum.com =SUMPRODUCT((YEAR(A1:A100)=2004)*(MONTH(A1:A100)=11)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "jdavis3874 >" <<jdavis3874.14770b@excelforum-nospam.com> wrote in message news:jdavis3874.14770b@excelforum-nos...

Concatenate function
Hi, How can I concatenate these 2 cells: one is time and the other is text: 9:00 and AM and I want the result to be 9:00 AM =CONCATENATE(AD2, " ", AE2) I have tried different formating cells but it doesn't work, this is what I get: 0.375 AM Thanks for your help. NSNR - You must format the time (which is a number) to text: =TEXT(AD2,"H:MM") & " " & AE2 -- Daryl S "NSNR" wrote: > Hi, > How can I concatenate these 2 cells: one is time and the other is text: 9:00 > and AM and I want the result to be ...

If statement with date
I'm trying to return new salary information based on a hire date. The criteria is if you were hired on or before Jan. 1, 2005, you get 3% of the mid-point of your current salary range. I have the spreadsheet set up as follows: A1 B1 C1 D1 E1 Current sal hire date PG mid-point new salary 40,950.00 12/15/03 15 52,460.00 ???? I was using the formula in E1: =If(B1>1/1/05,(D1*.03)+A1,A1) But when I entered a hire date that was after 1/1/05, it was still adding ...

How to convert date to text
I have a field - date. Let's say that one of the values is 6/20/05. How can I convert it to a number field, for example 6/20/05 will be shown as 20050605? Thanks, galsaba =TEXT(A1,"yyyymmdd") -- HTH RP (remove nothere from the email address if mailing direct) "galsaba" <galsaba@aol.com> wrote in message news:1116789405.609939.308320@g47g2000cwa.googlegroups.com... > I have a field - date. Let's say that one of the values is 6/20/05. > How can I convert it to a number field, for example 6/20/05 > will be shown as 20050605? > > Thanks,...

Undefined Function DATE
Access 2003 On 3 PCs the query works, on two PCs it gives the error message Undefined function DATE when I use Date() as a query Criteria Any ideas? Thank you. On Fri, 16 Nov 2007 17:12:15 -0500, Brendan on Comcast.net wrote: > Access 2003 > On 3 PCs the query works, on two PCs it gives the error message > Undefined function DATE > when I use Date() as a query Criteria > Any ideas? > Thank you. Those PC's have a missing reference. Open any module in Design view (or click Ctrl + G). On the Tools menu, click References. Click to clear the check box for the typ...

Concatenating Cells
I have spent hours this afternoon in Excel 2003 trying to concatenate two adjacent text columns into a third column defined as Text format. It doesn't work, the result cell just displays the formula you enter {e.g. =A1&B1 or =CONCATENATE(A1,B1)}. I discovered after a great deal of frustration that this will only work if the cell containing the formula is formatted as '*General'*. All the MS command help refers to the data being concatenated as 'text' data as does the command help that displays as you type. I found no help on this on the MS site and trawling the w...

weekending date of a given date
Hi All, If I have a past date of say fri 3/7/2008 and the weekending date for me is that sun the 9th, is there a way to get that date of the 9th? thanks, rodchar You can base it on offset from a well known Sunday date: DECLARE @dt DATETIME; SET @dt = '20080307'; -- 7 Mar 2008 -- Jan 7 1900 is Sunday SELECT DATEADD(DAY, (DATEDIFF(DAY, '19000108', @dt) / 7) * 7, '19000114') AS sunday_date; /* sunday_date ----------------------- 2008-03-09 00:00:00.000 */ -- Plamen Ratchev http://www.SQLStudio.com thanks for the help, rod. "...

Filltering data between two date ranges
Hi, wonder if anyone can help me - I have a list containing peoples birthdays. I wish to be able to enter a date range i.e. today and 2 weeks into the future. I then wish for the list to be filtered showing only those birthdays within the date range. The date range could span across 2 different months. I'm not particularly skilled in using features such as VBA etc, so if there is a simple solution it would be much appreciated. Many thanks for any help. Hi Dave! If you enter the birthdays as Month/Day this is somewhat easy. If you enter the birthdays as Month/Day/Year it is much m...

Payroll Manual Checks taking user date and not check Date
Hi everybody, I am facing a problem when posting the Payroll Manual checks, it's taking the user date as the check date and not the check date itself. Anyone who can help on this issue. Thanks, -- Khaled ASK Mr Khaled, I tested the scenario which is stated by you in Fabrikam company. I entered a manual check with posting date (01 April 07) on Batch Entry Windows, which is the check posting date in General Ledger. My Userdate is the (12 April 2007) and My Check Date is (10 April 07) i posted the check and its all fine. The Checkdate is exactly the Date i give for Check, My Post...

date shows none in out of box
Cannot send e-mails, I can compose an e-mail and it goes into the outbox but will not send. The date shows none in the outbox. Every e-mail I compose goes into the outbox but the date shows none. Please advise Michael wrote: > Cannot send e-mails, I can compose an e-mail and it goes into the > outbox but will not send. The date shows none in the outbox. Every > e-mail I compose goes into the outbox but the date shows none. > Please advise Version of Outlook? Type of Mail Server? If pop/imap, does it go if you do a Send/Recieve? Did it ever work? Any known changes between i...

sorting by date
Hi List, I want to sort by date, but its doing it by the day not the year. eg putting 1/2/1850 before 2/2/1849. How do you make it sort by year? Or would I need to put year in a separate column? Thanks Althea --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.591 / Virus Database: 374 - Release Date: 17/02/2004 Excel date system ends (or rather starts) on Jan 0, 1900 your dates are seen as text, it would definitely be easier if you used multiple columns.. -- Regards, Peo Sjoblom "AB" <ocean@wave.co.nz> w...

IF and Concatenate
Hi I have a following chart which list out delivery dates arcross the top with items and units on the body of the chart (dashes are spaces) On the right column is the results that I need. What kind of formula can I use to return such results? I thought that I can use IF and Concatenate formula (IF, ordered units, then seek out date....). Not even sure I can use IF, since in my real chart, I have more than 20+ dates going across. Any help would be much appreciated! Style---7/30---8/13----8/14----8/15---8/17--------Wanted-Result 66106-------------------------------9--------5--------9 DUE ...

When concatenating concatenates don't concatenate... #3
Sometimes the simplest thing... I didn't realise that all the files ha to be open for the concatenation to work :rolleyes: . Doing s eliminates the problem and explains the erratic behaviour. And yes, th idea is to blend the variable and unvariable parts of an html pag together by pasting the final concatenation into a notepad.txt file Primitive, but interim. Thank you very much for your help. Best regards d'A -- d'A ----------------------------------------------------------------------- d'Az's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1504 ...

How to format date as ddd, mmm, (date)
I would like Sunday, October 01, 2006 to show up as Sun Oct 1st Thanks for your help! ....Lisa...Excel 2003 Excel dates are days since Jan 00 1900 so one day is 1 etc. If you type in (assuming you are using US date formats) 10/01/06 in a cell, then use custom format ddd mmm d it will return Sun Oct 1, you can't format for ordinals like 1st, 2nd 3rd etc, you would need either an event macro or a formula in another cell and neither is really a good solution. You can of course type in Sun Oct 1st but it will be text -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Exc...

OL2002 Messages delivered do not appear immediately
2 of our 40+ users are experienceing the following problem with the e-mail system. One user with XP Pro OS and the other Windows 2000, both using OL2002. Whith the OL2002 opened and or minimized, messages are being delieved but are not showing up in their OL2002 immediately. If they decide to close OL2002 and open it up again, the e-mail message is there. People will send e-mail and the e-mail does not show up immediately through the Echange 5.5 network. The problem happens approximately 50% of the time. All the settings are exactly like all the other work stations using OL2002. It i...

select query including dates outside of specified range
Hi all, I have a query set up for my report that is supposed to return all records where the Admit Date is before or equal to the selected date, AND the Discharge Date is after the Selected Date. In the criteria for the Admit Date field, I put: =<[Requested Date] In the criteria for the Discharge Date field, I put: Is Null or >[Requested Date] The results for Discharge Date are fine. No records with a Discharge Date before or equal to the Requested Date appear. Not so good for Admit Dates. I get back all records with the appropriate Discharge Dates, even those with Admit Dates AFT...

How do I insert objects in Excel and include them in a drop-down .
I'm trying to create a drop-down list with smileys for customer appreciation levels. I can't find the way to insert them in a cell and include this cell (and content) in a list of choices. Hi AFAIK this is not possible -- Regards Frank Kabel Frankfurt, Germany "NatRob" <NatRob@discussions.microsoft.com> schrieb im Newsbeitrag news:D72B9AEB-4A1D-49DE-A4C5-5DE74A196CD9@microsoft.com... > I'm trying to create a drop-down list with smileys for customer appreciation > levels. I can't find the way to insert them in a cell and include this cell > (and con...

IsNull Not Working For Date
Hello, I have a query established to provide data that populates a report. For those fields that the user did not enter in any information, I want the report to show the text "Not Entered." I have successfully created the correct code in the query to handle all information except my birthday field, which is in date format. Here is the query code: Birthday: IIf(IsNull([EeBirthday]),"Not Entered",[EeBirthday]) EeBirthday is the field is should pull if it is not null. When I select datasheet view from the design view, nothing happens. No errors, highlights, nada. Whe...

Toolbar Button image always appears depressed
When I assign a message ID to a toolbar button that has been assigned by the Framework (such as ID_FILE_NEW), the button appears and functions normally. However, when I assign it a message ID that I have specially generated (which is trapped and handled by my OnCommand() override), the button functions normally but always appears depressed. What method do I need to write or override to make the button paint correctly, and appear depressed only when snapped? I am creating the toolbar like this from within my CFrameWnd derived class's OnCreate() function: m_wndToolBar.CreateEx( th...

Concatenate cells without specifying/writing cell address individually
Hi, Let's say I have text "we45t" in A1, "yuui6" in A2, "sfdgfd5" in A3 and so on till A45. (basically the text in each of the columns are different/random) Now I have to concatenate all the cells from A1 to A45 (with a single space between any 2 joinees) in to a single cell B1. I can get the job done by using a) = Concatenate (A1," ",A2," ",A3," ",A4," ",A5," ",A6," ",.......) and so till A45 b) =A1&" "&A2&" "&A3&" "&A47" "&A5&...

Subtracting Times with dates
I have 4 colums on for start date one for start time one for end date and one for end time I need to find a formula that will give me just the difference in the two times they may be two to 3 days different in the dates. example 3/1/2010 14:30 3/3/2010 22:30 need it to give me the answer of 56:00 =B1-A1 Custom format: [h]:mm "Tstewart14" wrote: > I have 4 colums on for start date one for start time one for end date and one > for end time > > I need to find a formula that will give me just the difference in the two > times they may be t...

Date Formatting
With the help of Biff yesterday I entered the formula ="Closing Balance at "&TEXT(E4,"dd-mmm-yyyy") which returned Closing Balance at 31-Mar-2010 exactly as I wanted - almost. My superiors require that to conform with the rest of the document the month element of the date be in upper case, i.e. Closing Balance at 31-MAR-2010 I have tried, in vain and in desperation, the following - ="Closing Balance at "&TEXT(E4,"DD-MMM-YYYY") and ="Closing Balance at "&TEXT(UPPER(E4),"dd-mmm-yyyy") but these do not have th...

Last Date in List
Hi I have a table set out as follows, Account Date Text Amount 31405535 01/01/04 nnnn 100000 31405535 12/12/03 tttt 222222 31405535 03/03/05 ttt 122456 There are 5 different accounts which could be entered under Account. The list is a export from out bank statement, I would like a formula to display the last date of transaction per account number. For eg in the above list I would like the formula to return 03/03/05. Can anyone help? TIA Enter your list of account numbers in a column, let's say Column F, starting at F2... G2, copied down: =LOOKUP(2,1/($A$2:$A$100=F2),$B$...