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

Answer 1

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.

Answer 2

Try the code

="today is "&TEXT(E16,"yyyy-mm-dd")

http://www.mrexcel.com/forum/excel-questions/513984-linked-text-today-problem.html

Answer 3

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

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

More info:

Excel Date Formatting