Formatting selected dates

[jomili]

I'd like to be able to select a date in my Word document and convert it from something like "7/31/15" to "31st July, 2015". The macro below almost does it, but doesn't allow for selecting or inputting a date, and insteads inserts today's date. I've fooled around with selectng or inputting a date but can't get it to work. Any help would be appreciated.

Please Login or Register  to view this content.

[daffodil11]

Please Login or Register  to view this content.

[jomili]

Daffodil,
That's 98% there. The only issue I see is that the ordinal is not appearing as superscript.

[daffodil11]

I'm not sure I see the issue. Perhaps you've chosen a font that doesn't do superscripts?

[jomili]

Nope, I've tried with Courier New, TimesNewRoman, and Calibri, and get the same results each time. See the picture. Any ideas?

[macropod]

Assuiming you can use calendar content controls in your document, try the attached. the use of such a content control will ensure the input of a valid date. Even if you can't use one, though, the code shows how the superscripting can be implemented.

[jomili]

Macropod,
The content control seems to be specific to the document, and the area in the document, if I'm understanding your macros. But what I'm wanting is more of an "on the fly" solution. I get hundreds of Word and Excel documents sent to me, and quite often I have to change a date (not all dates) from something like "7/31/15" to "31st July, 2015". Daffodil's code works for Word, only issue is I'd like the oridinal to be in superscript, and even though that functionality works for him it's not working for me.

[daffodil11]

I've done some testing with it and was able to occasionally produce the same issue. I believe it has to do with how much is selected.

When selecting the date only, it reliably produces the intended result, but when selecting even a single proceeding space it sometimes omits the superscript.

[macropod]

So, do you want a macro that requires you to select the dates to be reformatted, or would you prefer one that searches out the dates in the document and asks which ones you want to reformat? Also, I note that the dates you've asked for help with are in the US m/d/yyyy format. Are all the dates formatted that way?

[jomili]

Daffodil,
Maybe it also depends on what day you run the macro; I tested it again this morning and it worked flawlessly, even whan I selected a space in front. So instead of my earlier "98%", lets bump this one up to 99% since you saw some glitches.

Macropod,
I was thinking of simply modifying the selected date, but I could see the capability of doing it document-wide at times. Since I live in the US and work with State and Federal goverment, most of my dates are in US formats mm/dd/yyyy format, mm/dd/yy, m/d/yy or m/d/yyyy.

[macropod]

Try the following macro, which will interactively reformat all dates in a selected range of text.

Please Login or Register  to view this content.

[jomili]

Macropod,
Great start! Because the dates come to me in every conceivable combination I tested the macro by feeding it dates formatted like the following:
03/08/15, 3/8/15, 3/8/2015, and 03/08/2015 .

Results were: "08th Mar, 2015, 8th Mar, 2015, 8th Mar, 2015, and 08th Mar, 2015"

So, in these cases where we have a date input as 03/08, how do we keep the "0" out of the result?
And, how can we get "March" instead of "Mar"?

[macropod]

Try changing:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

[jomili]

Hmm, that took off the "0" at the beginning, but jacked up the dates. For instance, using "03/07/14", expected result should be "7th March, 2014", actual result is "3rd July, 2014"

[macropod]

Hmm, that took off the "0" at the beginning, but jacked up the dates. For instance, using "03/07/14", expected result should be "7th March, 2014", actual result is "3rd July, 2014"

The result I get using "03/07/14" is "7th March, 2014"...

You could try:
StrTxt = CInt(Split(.Text, "/")(1)) & " " & Format(Split(.Text, "/")(0) & "/" & Split(.Text, "/")(2), "MMMM, YYYY")

[jomili]

Just to make sure I hadn't imagined my last results, I tried "03/07/14" again. Still came out as 3rd July,2014.

So I then changed

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

After the change I tested by selecting these dates:

Please Login or Register  to view this content.

Results:

Please Login or Register  to view this content.

So we're extremely close. How do we put in an exception to handle a date inputted like 3/7/14?

[macropod]

Try:
StrTxt = CInt(Split(.Text, "/")(1)) & " " & Format(Split(.Text, "/")(0) & "/0", "MMMM") & " " & Format(CDate("1/1/" & Split(.Text, "/")(2)), "YYYY")

[jomili]

Wow, looks like 100%. I tried to trip it up by using a menagerie of dates, and it worked flawlessly! Thank you so much for pressing on to a solid solution!

[macropod]

Depending on your regional settings, you may instead be able to use:
StrTxt = Format(CDate(.Text), "D MMMM, YYYY")

[jomili]

Yes, that works absolutely fine for me, and is much easier to understand. Thank you!