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

  • Thread starter Thread starter rbrown999
  • Start date Start date
R

rbrown999

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
 
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
 
Back
Top