Formula Help - Converting Date String into an Actual Date

[Johnny Thunder]

Hello All,

Working on a weird issue here. Look on the interwebs and found nothing for my specific issue. The DateValue syntax doesn't seem to work in the event the data string makes no sense.

So here is how some of the dates come in;

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date as it comes in[/TD]
[TD]Converted to General[/TD]
[/TR]
[TR]
[TD][TABLE="width: 191"]
<colgroup><col></colgroup><tbody>[TR]
[TD]10/31/2019 11:00 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 149"]
<colgroup><col></colgroup><tbody>[TR]
[TD]43769.95833[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 191"]
<colgroup><col></colgroup><tbody>[TR]
[TD]01/01/2055 11:00 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 149"]
<colgroup><col></colgroup><tbody>[TR]
[TD]56615.95833[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 191"]
<colgroup><col></colgroup><tbody>[TR]
[TD]10/14/2019 10:52 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 149"]
<colgroup><col></colgroup><tbody>[TR]
[TD]43752.95278[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]









So, it seems that the issue is coming from the system putting in the last two characters in the date incorrectly. I think the system actually puts in "20" and excel is converting that to "2055" for some reason. Does anyone know of a way to convert this automatically going forward so that when "21" shows up the formula can handle that without doing Find/Replace?

 

[Scott Huish]

If I type 1/1/55 11:00 PM, my Excel converts that to 1955 not 2055.

How did you get that exactly and what is this 21 you are talking about?

 

[Johnny Thunder]

1. Sorry, I wasn't clear, in our web based system people have entered in the year as a 2 digit format which is causing the error of having the output show up 2055. I think the system is actually trying to resolve the data and spits out the "2055". Hopefully there is a trick to clean this up thru a formula?

2. I meant 2021 when i wrote "21" similarly how I was mentioning "20" for 2020. Hope that clarifies. I just meant that in the event the code throws another weird year in the date that whatever solution that is proposed it will account for any variation of incorrect year. I was hoping that maybe there was something embedded in the weird "56615.95833" formatted version of the date that would provide a key to creating the correct date.

 

[Scott Huish]

The General format shows the date as Excel stores dates, The Integer part is the number of days since January 1, 1900. The decimal part is the fractional part of the day to represent time (.5 is 1/2 a day and is noon, for example).

I guess you could check for future years, what would be the most amount of years ahead of today's date that would still be deemed acceptable?
2055 is 36 years from now. So at what point do you determine whether the year should be in the past or in the future?