Excel issue with "=TODAY()" and adding it to another cell with other values
As you can see below I'm unable to get the =TODAY() "correct" information over to another column. And I'm really hoping that there is some way to actually get this to work.
I'm fairly new to excel so I'm just using a =column&column&column etc.
I'm trying to get DDMMYYYY data correctly into column "B".
I'm currently just using a basic formula"=F11&C11&G11&D11&H11&E11&I11&O11&P11&J11&K11&E11&L11&D11&Q11&E11&R11&M11&N11&E11&S11&B1" where O11 is where i have the DDMMYYYY.
Solution 1:
Background
Excel stores dates and times in units of days. The date is stored as the (integer) count of days since January 0, 1900; we passed day 43,000 in September of last year. Time is appended to the date as the decimal fraction of a day (noon is 0.5 day).
Date (and time) information is displayed in readable form by formatting it. There is a collection of built-in formats to display date and time information in commonly used forms. Excel also includes a collection of functions that let you extract and use the common date and time components.
If you simply refer to a date or time value in a cell, you will be working with the raw internal representation rather than the formatted version displayed. That's why your TODAY() value is displayed formatted as DDMMYYYY (15042018), but your reference to that cell produces a value of 43205. If you want to use a DDMMYYYY translation of the cell value in your string, you need to convert the raw cell value to that form.
Solution
The simplest solution may be to use the TEXT function and include the format spec. The TEXT function converts a numerical value to a formatted representation of the value. It can translate Excel date/time values to readable forms as well as format other kinds of numbers. A useful guide to the TEXT function: https://support.office.com/en-us/article/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c
In your worksheet, cell O11 is the cell with the TODAY() function. Instead of concatenating the raw cell reference (...&O11&...), replace the cell reference with
TEXT(O11,"ddmmyyyy")
like ...&TEXT(O11,"ddmmyyyy")&...
That formats the value of TODAY() in your string to the DDMMYYYY format.
Version Issue
Note that the problems you describe in the comments are due to selecting an ancient Excel format for the file. You're using Excel V2016, but selected a file format limited to V97-2003. That format isn't capable of features more recent than the 2003 version. The TEXT function dates back to V2007, and it's surprising that you haven't run into other unsupported features.
Save the file as "Excel Macro-Enabled Workbook", close it, open the saved version, then put in the TEXT function.