Hi and help
I trying to have a drop down which brings back a row rather than one cell
as in
20 Jan 16 2.00pm INDOOR TEAM Heathfield away WHITE 2R
I can do it by adding all the values into on cell by then the date part becomes a number format not a date and I need to sort the drop down
Regards
Gerry
As far as I understand your “value of a row” is meant to be some concateantion result starting with a date-time value.
You know that that concatenated date-time value only can be text the actual value was converted into using the TEXT function. (Otherwise, converted automatically, it would look like a decimal number.)
Dates, time values, and date-time values like general numerics, too, converted into text can only be sorted correctly if they are converted based on a strict format ensuring
Back to Date-Time specifically:
The ISO 8601 standard (which is the only internationally recognised one) is made to exactly ensure the above conditions. You may use it slightly adapted to human preferences reading dates:
=TEXT(Date+Time;“YYYY-MM-DD HH:MM:SS”)&Delimiter&TheRestOfTheRow
(You may omit the “:SS” or - to the contrary - append “.00” for two decimals e.g. depending on the accuracy you want.)
am/pm is a deprecated remnant from very old times. An absolute no-go with this respect.
Hi
I found the answer.
I needed the data as a reference to a vlookup and simply changed the reference from a 1 or 2 digit number to a 3
eg start at 100 then
=(Text(AP2,“ddd dd mmm”))&" “&AQ2&” “&AR2&” “&AS2&” “&AT2&” “&AU2&” “&AV2&” "&AO2
gives in cell B3
Sat 20 Feb 2.30pm FRIENDLY website trial match home WHITE 3 TRIPLES 101
and =VLOOKUP(RIGHT(B3,3),BC2:BE80,2,0) gives the right answer.
i would like to hide the 101 but that’s proving difficult