Hi,
My problem is the following :
We often receive reports with dates, a start and end date in adjoining columns. These cells containing the dates are always in text format. On some reports, the dates show in the standard way, on soem they show in the inverted US way (MM/DD/YYYY).
I’m trying to come up with an easy, cut and paste formula to share with my team that will make it easy to concatenate these dates into the following format : DD/MMM/YYYY - DD/MMM/YYYY (the first being the start date and the second being the end date). I want to use the MMM format to avoid confusion further on .
What I’ve got so far is this (for the US date format, with the month at the begining):
=MID(E3,4,2)&"/"&TEXT(LEFT(E3,2),“MMM”)&"/"&RIGHT(E3,4)&" - “&MID(F3,4,2)&”/"&TEXT(LEFT(F3,2),“MMM”)&"/"&RIGHT(F3,4)
Column E contains the start date, column F holds the end date. It returns everything nicely formatted, but for some reason I cannot get it to recognise the number designating the month. In my example, I was using dates in May (05), but the results show January.
Does anybody have any ideas how I can get the months displaying correctly? Any help is much appreciated!