Excel: Format TODAY() / DATE

Question

1) I would expect in E14 "Today is 13.09.2016", because E16 is formatted as DATE (customer)! But there is a general number instead of date. Why is Excel not able to copy the content of E16 !! How can I get the date in this position?

PS: If you suggest me ="today is "&today(), then please explain me, how I can format the date in YYYYMMDD as in the screenshot

Solution 1

You need to use another function to apply formatting, try the following:

="today is " & TEXT(TODAY(), "yyyymmdd")

More info:

Excel Date Formatting

Solution 2

1. Put =TODAY() in A1.
2. Tap Ctrl+1 and format A1 as Number, Custom, Type: To\d\a\y i\s yyyymmdd

You will display This is 20160913 but retain the underlying raw date that can be used in calculations.

Comments

[Clusks]

As a workaround, does ="today is "&MID(E16,1,4) &MID(E16,5,2)&MID(E16,7,2) work?

[ng-User]

NO, same error as below "screenshot"

[Ajay Dsouza]

The logic here is that it is text in the cell, so it needs to be text. And text function needs the format to be specified.