Excel: Getting Date Formats to Match

[mathias1979]

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.

 

[xlhelp]

Have checked your Regional Settings?

Member- AAAA Association Against Acronym Abusers

 

[mathias1979]

Where do I check regional settings, and what might I look for here?

But a clarification, the dates that I am hard coding in, in the cell the still show the proper format (01/01/2000 00:00)...but when I highlight the cell, it is in the function box (?) where it shows up as 1/1/2000 12:00 AM.

 

[combo]

Do not play with strings, create regular dates (they are numbers behind) and do not bother displayed formats.

combo

 

[mathias1979]

unfortunately I have no choice but to use strings to create the dates...there are simply too many to do it manually, and the increments between dates/times is variable, so simply using the TIME function to increment the dates is not an option.

 

[mathias1979]

I believe I have solved the problem. After converting my date text string to a value, I had to the copy those dates using paste special to paste on the values, not the formulas. Still, thanks for the input!

 

[AnotherHiggins]

unfortunately I have no choice but to use strings to create the dates

Sure you do!

Just wrap your formula in VALUE()

Example:
=Value(CONCATENATE(MM,"/",DD,"/",YYYY," ",HH,":",MM))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.