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

[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?

 

[Roger Govier]

Hi

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

 

[Niek Otten]

=TEXT(TODAY(),"mmdd")

 

[rbrown999]

=TEXT(TODAY(),"mmdd")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel






- Show quoted text -

That did it, thanks!!

 

[David McRitchie]

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