Using =now() to display date format like "mmdd"

I am building a quoting tool and for the quote number, I am trying to
concatenate "QUO", the first 3 characters of the customer name, and
the month & date.  So what I want is:

QUO-SON-0225

But when I use the "NOW" command for the date, I get:

QUO-SON-39869.3365444444

How can I reformat the result of the "NOW" command to get just the
"mmdd" that I'm looking for?
0
rbrown999 (10)
2/25/2009 1:29:40 PM
excel 39879 articles. 2 followers. Follow

4 Replies
1053 Views

Similar Articles

[PageSpeed] 20

Hi

Try
="QUO-"&LEFT(Customer,3)&"-"&TEXT(NOW(),mmyy")
where Customer is the cell ref containing the Customer name
-- 
Regards
Roger Govier

<rbrown999@gmail.com> wrote in message 
news:fdb14440-f494-47fc-b7e7-90deb72f7d36@13g2000yql.googlegroups.com...
> I am building a quoting tool and for the quote number, I am trying to
> concatenate "QUO", the first 3 characters of the customer name, and
> the month & date.  So what I want is:
>
> QUO-SON-0225
>
> But when I use the "NOW" command for the date, I get:
>
> QUO-SON-39869.3365444444
>
> How can I reformat the result of the "NOW" command to get just the
> "mmdd" that I'm looking for? 

0
Roger
2/25/2009 1:39:16 PM
=TEXT(TODAY(),"mmdd")

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

<rbrown999@gmail.com> wrote in message 
news:fdb14440-f494-47fc-b7e7-90deb72f7d36@13g2000yql.googlegroups.com...
>I am building a quoting tool and for the quote number, I am trying to
> concatenate "QUO", the first 3 characters of the customer name, and
> the month & date.  So what I want is:
>
> QUO-SON-0225
>
> But when I use the "NOW" command for the date, I get:
>
> QUO-SON-39869.3365444444
>
> How can I reformat the result of the "NOW" command to get just the
> "mmdd" that I'm looking for? 

0
nicolaus (2022)
2/25/2009 1:41:49 PM
On Feb 25, 8:41=A0am, "Niek Otten" <nicol...@xs4all.nl> wrote:
> =3DTEXT(TODAY(),"mmdd")
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> <rbrown...@gmail.com> wrote in message
>
> news:fdb14440-f494-47fc-b7e7-90deb72f7d36@13g2000yql.googlegroups.com...
>
>
>
> >I am building a quoting tool and for the quote number, I am trying to
> > concatenate "QUO", the first 3 characters of the customer name, and
> > the month & date. =A0So what I want is:
>
> > QUO-SON-0225
>
> > But when I use the "NOW" command for the date, I get:
>
> > QUO-SON-39869.3365444444
>
> > How can I reformat the result of the "NOW" command to get just the
> > "mmdd" that I'm looking for?- Hide quoted text -
>
> - Show quoted text -

That did it, thanks!!
0
rbrown999 (10)
2/25/2009 2:03:12 PM
Assuming that you want to assign the text when entered, and
not have it change when formulas are reevaluated, would
suggest you use an Event macro.

You can use NOW for date + time, or you can use DATE or you can use TIME. 
Install by right-click on tab,   choose "View Code",  use the following code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column <> 2 Then Exit Sub
  If Target.row = 1 Then Exit Sub
  If IsEmpty(Target(1)) Then Exit Sub
  If IsEmpty(Target.Offset(0, -1)) Then
    Target.Offset(0, -1) = "QUO-SON-" & Format(Date, "mmdd")
  End If
End Sub

More information on Event macros in
  http://www.mvps.org/dmcritchie/excel/event.htm
-- 
HTH,
David McRitchie  
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm

 

"Niek Otten" <nicolaus@xs4all.nl> wrote in message news:#c23I70lJHA.4372@TK2MSFTNGP02.phx.gbl...
> =TEXT(TODAY(),"mmdd")
> 
> -- 
> Kind regards,
> 
> Niek Otten
> Microsoft MVP - Excel
> 
> <rbrown999@gmail.com> wrote in message 
> news:fdb14440-f494-47fc-b7e7-90deb72f7d36@13g2000yql.googlegroups.com...
>>I am building a quoting tool and for the quote number, I am trying to
>> concatenate "QUO", the first 3 characters of the customer name, and
>> the month & date.  So what I want is:
>>
>> QUO-SON-0225
>>
>> But when I use the "NOW" command for the date, I get:
>>
>> QUO-SON-39869.3365444444
>>
>> How can I reformat the result of the "NOW" command to get just the
>> "mmdd" that I'm looking for? 
> 
0
2/25/2009 7:50:28 PM
Reply:

Similar Artilces:

how do I convert date format yyyymmdd to mm/dd/yyyy
how do I convert date format yyyymmdd to mmddyyy I have rows of dates displayed as yyyymmdd (ie 20100131) I want them displayed as regular dates (ie 01/31/2010) If it's a column of cells... Select the column Data|text to columns (in xl2003 menus) choose fixed width, but don't have any delimiter lines Choose Date (ymd) This will convert the data to dates. Now you can format the ranyge the way you like. Datahead wrote: > > how do I convert date format yyyymmdd to mmddyyy > > I have rows of dates displayed as yyyymmdd (ie 20100131) > > I wan...

Convert Date YYYYMMDD
Hi All, I need to convert using a query the date format from MM/DD/YYYY to YYYYMMDD. How do I do this? Thanks Matt -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200803/1 Format([YourDateField], "yyyymmdd") -- KARL DEWEY Build a little - Test a little "mattc66 via AccessMonster.com" wrote: > Hi All, > > I need to convert using a query the date format from MM/DD/YYYY to YYYYMMDD. > > How do I do this? > > Thanks > Matt > > -- >...

Date format change from yyyymmdd to mm/dd/yyyy
Need to change the date format from yyyymmdd to mm/dd/yy. Tried setting up a custom format with no results Select the column, do data>text to columns, click next twice and under column data format select date, from scrolldown select YMD and click finish. There is no way excel could know that a number like 20031203 is a date as opposed to a number just by formatting, since excel dates are days since Jan 0 1900 and a date like 12/03/03 is equal to 37958, then the number 20031203 will just return ####### if formatted as a date -- Regards, Peo Sjoblom "Peter" <anonymous@di...

Using Date and time in YYYYMMDDHHMMSS format automatically
I have a question about using date and time in the following format: yyyymmddhhmmss, i.e. 20070328144801 I have a column in a table called ScLoadTime. I am trying to do an query which will automatically show me everything between yesterday at 17:00:01 and Today at 17:00:00 I am using the following: And ((ScLoadTime) Between Format(Date(),"yyyymmdd""170000""") And Format(DateAdd("d",-1,Date()),"yyyymmdd""170001""")) The problem is the query comes straight back with no results. If I changed the query to And ScLoadTime b...

Auto Formatting the date field from mmddyyyy to mm/dd/yyyy
Hello, I have the current script in place to format date fields from mmddyyyy to mm/dd/yyyy. I am trying to reduce every possible keystroke and this will eliminate 2. My issue is that CRM fires it's validity check on the date field before the onchange event. Thus, it finds an error in the date because it hasn't had a chance to format. Any suggestions on circumventing the CRM validity check or any other work arounds? Thanks, Brandon // Get the field that fired the event. var oField = event.srcElement; // Validate the field information. if (typeof(oField) != "undefined&...

need to convert microsoft date to yyyymmdd
I have a file in which I concatenate two fields, the vendor and the date. it shows as 40255-3042 I would like 20100318-3042 Here's my formula: =concatenate(O2,"-",B2) Can anyone help me format the date field I am picking up in column O2? =text(o2,"yyyymmdd")&"-"&b2 or maybe... =text(o2,"yyyymmdd")&"-"&text(b2,"0000") To make sure that that vendor is 4 digits, too. pm wrote: > > I have a file in which I concatenate two fields, the vendor and the date. > > it shows as 40255-3042...

Converting Dates from YYMMDD to MMDDYYYY
Hi All; I'm looking for a format function that will convert a date in the text format of YYMMDD to text format of MMDDYYYY. Any help would be GREATLY appreciated! Thanks in advance! You can't do it using a Format function, but try: Function SwitchDateFormat(YYMMDD As String) As String Dim strDay As String Dim strMonth As String Dim strYear As String If Len(YYMMDD) = 6 Then strYear = Left$(YYMMDD, 2) strMonth = Mid$(YYMMDD, 3, 2) strDay = Right$(YYMMDD, 2) If strYear < "30" Then strYear = "19" & strYear Else strYear =...

yyyymmdd format??????
Does anyone know how to format a date in access or excel to accept and treat the date in a text format "yyyymmdd" as a date? I import data in text format in access, but I need to convert the date to a format that access can handle for doing reports (grouping, etc.) any ideas? thanks in advance. Clay Hi Clay, Import the date to a text field. Then use an calculated field like this - replacing "TheDate" with the name of your date/time field and "x" with the name of the text field - in a query to convert it to an Access date/time value: TheDate: DateSerial(C...

Convert mm/dd/yyyy to yyyymmdd Date format in Excel
Seems like it should be easy but I am stuck. Any easy way to convert mm/dd/yyyy format in column to yyyymmdd forma some of the values in my database column for mm or dd have only 1 digit, and would need 0 fillers in order to comply with my final database need Help Thanks in advance Click FORMAT > CELLS > Custom. In the text box in the middle of the dialog box type yyyymmdd then click OK. In that cell(s) if you type 1/20/2003 then hit enter 20030120 will be displayed. Marty >-----Original Message----- >Seems like it should be easy but I am stuck.. > >Any easy w...

Convert text date to mmddyyy date
I have 3 columns containing the date. A=Month (Text-January) B=Day (Numeric 1-31) C=Year. I need to combine these 3 columns into one column in the date format mm/dd/yyyy. Thanks for your help! Try this in D1: =--(B1&A1&C1) and drag down Format the range as a date. that concatenation results in a string that looks like: 12January2008 The first minus coerces the date into a number (but a negative number). The other minus changes it to a positive number. And since dates are just plain old numbers (formatted nicely) in excel, it may even work! wx4usa wrote: >...

Converting YYYYMMDD Special Format to Text
What formula or function converts a cell with a special date format of YYYYMMDD, lets say 20030801, to the text string "20030801"? In my case the special format option YYYYMMDD makes the number appear as 20030801 but will only display the actual date value of 37834 when attempting to convert the date to a text string. One way: =TEXT(A1, "yyyymmdd") where A1 contains your value. In article <108901c38960$b4c9b6d0$a301280a@phx.gbl>, "Mike" <mbredal@tetontel.com> wrote: > What formula or function converts a cell with a special > ...

Dates in Excel
I have a column of dates, they are in yyyymmdd format (no slashes or anything) and they're text. I cannot format them, how do I do this? Basically, I want to change 19700429 to 4/29/1970, but for a whole column of different dates (dates not in sequential order). Thanks in advance!!!!! Insert a helper column. Then, assuming your "19700429" is in cell A1, paste the following formula into B1. =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Copy the formula down and you'll be all set. Then if you want to get rid of the helper column, copy the values in column B, and paste ...

How to put Time or Date Only in Cell 1) Time HHMMSS 2) Date YYYYMMDD
I need to have a cell = the current time in the format HHMMSS And when I export as Comma Delimited or Fixed Length Ascii, I need for it to remain in that format. Ditto for Date as YYYYMMDD I've played around with both =TIME and =DATE but no luck. Can anyone tell me how to do this? AND, once the forumula is in the cell... how can I get it to update to the current time or date? thanks for any help I formatted my times as hhmmss and my dates as yyyymmdd and then did a file|saveAs. I chose the comma separated values (*.csv) and I got what you wanted. I opened it in notepad to ver...

Using =now() to display date format like "mmdd"
I am building a quoting tool and for the quote number, I am trying to concatenate "QUO", the first 3 characters of the customer name, and the month & date. So what I want is: QUO-SON-0225 But when I use the "NOW" command for the date, I get: QUO-SON-39869.3365444444 How can I reformat the result of the "NOW" command to get just the "mmdd" that I'm looking for? Hi Try ="QUO-"&LEFT(Customer,3)&"-"&TEXT(NOW(),mmyy") where Customer is the cell ref containing the Customer name -- Regards Roger Govier <r...

How to format yyyymmdd with Button and VBA ??
I tried this... Sub Button8_Click() Sheets("Sheet1").Select Range("C10").Select ActiveCell.FormulaR1C1 = Now() ActiveCell.AutoFormat = yyyymmdd ' WRONG End Subf But the last line is not correct. I don't know how to code it. Can anybody help me out? thanks Sub Button8_Click() with worksheets("sheet1").range("c10") .value = now 'include the time, too??? ' .value = date 'if you want to avoid the time. .numberformat="yyyymmdd" end with End Sub You don't ...

Convert columns from yyyymmdd (text) to dd/mm/yyyy (date)
Hi At the moment I'm trying to move some data from our company mainframe to a SQL 2000 database. I've been given a delimited file which I put into Excel before using DTS to import into sequel. However, all dates in the CSV are stored as yyyymmdd and as a text format in excel. I've searched these forums for answers on how to convert these fields to a dd/mm/yyyy format, but I couldn't get any suggestions to work properly. (It doesn't really matter if excel changes it to a string of 'dd/mm/yyyy' as opposed to a date field long as it's in that format. I'd be r...

m/d/yyyy h:mm date conversion to yyyymmdd ???
Is this doable??? I want to use the functionality of excel/vb to change the actual current value of a cell, for example, 7/9/2003 9:41 to "20030709". The current format of the cell is custom m/d/yyyy h:mm. I need the actual value of the cell to be 20030709, not a formula or the excel date/time serial number. I am uploading this file to a Physical File on an AS/400. It's easy enough for me to manipulate the field once I get the data to the AS/400, I just would like to try to format the data correctly in the spreadsheet before uploading...just looking to see what I can/ca...

Converting date from cyymmdd to dd/mm/yy and sorting pivot table
Dear All, I am pulling in data from an AS400 which brings in the date field in native cyymmdd format. I want to sort the pivot table in date order sequence but show the date as dd/mm/yy. I tried pulling a dd/mm/yy formated date from the AS400 and sorting on this but the sort order did not work correctly for some reason, so i guess sorting in cyymmdd order is the only way ? Many thanks for any advice Regards Hi one idea: if you import the dates as dd/mm/yyyy they're probably stored not as date but as text values in Excel. Try the following after the import: - select an empty cell a...

date in the form of yyyymmdd
other than entering 'yyyy-mm-dd' as date, are there ways to enter dates "yyymmdd" if I enter, for example, 20070101 and change the cell format to yyyymmdd and the cell becomes lots of #. Look here: http://xldynamic.com/source/xld.QDEDownload.html or here: http://www.cpearson.com/excel/DateTimeEntry.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "kang" <kang@hao.com> wrote in message news:eExl1$WCIHA.1184@TK2MSFTNGP04.phx.gbl... | other than entering 'yyyy-mm-dd' as date, | are there ways to enter dates "yyymmdd" | | if I enter...

query from a form Date as number YYYYMMDD
I have a clients who's dates in thier database are all stored as numbers formatted YYYYMMDD (always 8 characters) I would like to run a query from a form with the date parameter entered by the user as a date then convert it in the query as the number format needed. I can convert the number to date with DateSerial but I don't know how to convert the date to number with the correct format. Thanks! Angie Rather than try to derive the number of the date, format the number as a date. In this example, x = the numeric field that carries the date(in about the strangest way I have eve...