mathias1979
Technical User
I need to create a lookup table for dates, in the format mm/dd/yyyy hh:mm. My problem arises in trying to get the formats of the dates I am looking up to match the format of the dates in the lookup table.
The lookup table values are created by using the CONCATENATE function to string together text strings which contain month, day, year ect into the date (i.e. CONCATENATE(MM,"/",DD,"/",YYYY," ",HH,":",MM). I then convert the result to values, and specify them as a date, with a custom format (mm/dd/yyyy hh:mm)
For the values I am actually looking up, I am manually specifying the dates, by typing in the date string (01/01/2000 00:00),and I specify the same custom format (mm/dd/yyyy hh:mm). But for some reason, when I hard code in the dates, the leading zeros are dropped for months/days/hours that are less than 10 (so 01/01/2000 00:00 becomes 1/1/2000 0:00). Furthermore, AM/PM is being appended to the value even though I am not specifying it to be in the format. So the end result is I am unable to lookup the dates, because the formats aren't matching.
Any thoughts on how to rectify this? I suppose I could just create my lookup values the same way, hard coding in values for month, day ect then using CONCATENATE to join everything. But it seems there should be an easier way.
The lookup table values are created by using the CONCATENATE function to string together text strings which contain month, day, year ect into the date (i.e. CONCATENATE(MM,"/",DD,"/",YYYY," ",HH,":",MM). I then convert the result to values, and specify them as a date, with a custom format (mm/dd/yyyy hh:mm)
For the values I am actually looking up, I am manually specifying the dates, by typing in the date string (01/01/2000 00:00),and I specify the same custom format (mm/dd/yyyy hh:mm). But for some reason, when I hard code in the dates, the leading zeros are dropped for months/days/hours that are less than 10 (so 01/01/2000 00:00 becomes 1/1/2000 0:00). Furthermore, AM/PM is being appended to the value even though I am not specifying it to be in the format. So the end result is I am unable to lookup the dates, because the formats aren't matching.
Any thoughts on how to rectify this? I suppose I could just create my lookup values the same way, hard coding in values for month, day ect then using CONCATENATE to join everything. But it seems there should be an easier way.