CODEWITHSUNDEEP
excelgoogle-sheetsexcel-2010
Carol.Kar
Carol.Kar

Reputation: 5345

Set format of date, concatenated with string

I would like to get the following string as a date formatted:

="Status: "& TODAY()

However, I get:

Status: 42418

I would like to get:

Status: 18.02.2016

I already tried to change the format to Date or use the Datevalue function.

Any suggestions, how to get the current date correctly formatted?

Upvotes: 1

Views: 54

Answers (2)

daniel
daniel

Reputation: 648

A better way to do this is just change the number format.

Change your cell formula back to =today()

Select the Format menu, then Number, More Formats, and finallay More date and time formats

Then set the custom format as followed:

Date format dialog

The benefit of this is that the value of your cell is still a date, not string

EDIT

This answer primarily illustrate separation of presentation and data.

Think of currency. 2 is data, and US$ 2.00 is presentation of the data. When you compute the value, you just want to put the number 2 instead of a formula like = "US$" & data & ".00"

This makes the spreadsheet more robust to any future change where you want to reference your computed values in other cells.

Another example would be win-loss computation. It's better to output the value TRUE/FALSE or 1,0,-1 and then have a custom format to convert the value to text. (In this case, the format rule is "WIN";"LOSS";"DRAW")

Upvotes: 0

tonirush
tonirush

Reputation: 430

You can format the date manualy with DAY(),MONTH() and YEAR() function.

="Status: "& DAY(TODAY())&"."& MONTH(TODAY())&"."&YEAR(TODAY())

Or you can use the TEXT() function to do the formatting.

="Status: " & TEXT(TODAY();"DD.MM.YYYY")

Upvotes: 2

Related Questions