-3

I want to change so many dates saved as text in excel sheets to Arabic (Hijri) date format, ie; dd/mm/yyyy to (right to left) yyyy/mm/dd .. it can be done manually be change custom format from Number Format panel ( by choosing the location , calendar type and the right format from list ) then replace the text in cell by same value ..
I need VBA code to automate this replacement process for any ltr date and for existing rtl date just convert the format from general to date ..

another problem , there is some other text around the date in cell like ( dd/mm/yyyy ttt ) . I want the code to remove this text (ttt) ( any text ) and then change to the right format

I found this code but it is not work to my specific need
Changing the date format to yyyy-mm-dd

I appreciate any help , thanks in advance ..

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Abeer
  • 9
  • 1
  • 9
  • 1
    So change that code to work for your specific need. If you run into problems doing so, explain what issue you've encountered, *include the code you've written that isn't working for you*, and ask a **specific question** related to that code. We're glad to help, but we're not a code writing service. – Ken White Nov 12 '16 at 02:27
  • Sorry for any inconvenience Mr. @KenWhite , I will update my question with my code soon . – Abeer Nov 12 '16 at 11:36

1 Answers1

1

You have a number of separate problems and it is unrealistic to expect someone else to have posted a complete solution to that set of problems or that someone will code a complete solution for you. You need to split your total problem into its components and create or look for a solution to each component.

You have strings that contain CE dates in the format “dd/mm/yyyy”. These dates could be surrounded by text. You give the example “dd/mm/yyyy ttt”. Can ttt contain spaces? Could the “ttt” come before the date? Could the string be as complicated as “aaaa bbbb cccc 12/11/2016 dddd eeee ffff”?

Whatever the situation, I suspect something like:

Dim Part() As String

Part = Split(.Cells(R, C).Value," ")

would be the core of first step. With my complicated example, this would give:

Part(0) = "aaaa"
Part(1) = "bbbb"
Part(2) = "cccc"
Part(3) = "12/11/2016"
  :   :   :   :

A loop over the parts of each cell value looking for a string for which IsDate gives True would allow you to find the date so .Cells(R, C).Value = Part(N) would delete the unwanted text.

I would take a copy of your data and try to code a macro that discards the unwanted text. If you can successfully create that macro, you have completed step 1 of your solution. If you have trouble with this macro, you can ask for help here and expect to get it.

The next step is to convert the string “dd/mm/yyyy” to an Excel date. Excel holds dates as the number of dates since 1/1/1900 CE. Replacing: .Cells(R, C).Value = Part(N)by .Cells(R, C).Value = CDate(Part(N))ought to do the trick. However, Excel sometimes tries to interpret “dd/mm/yyyy” dates as “mm/dd/yyyy”. I think you will be alright but be aware of this possibility.

Your last step is to convert a date from the CE calendar to the Hijri calendar. This is not just a format issue. The two calendars have different year zeroes and different month lengths. There may be a standard conversion function in your country but there does not appear to be one here in the UK. There is help online so you should be able to find a function that will perform the conversion.

You have a number of separate problems and it is unrealistic to expect someone else to have posted a complete solution to that set of problems or that someone will code a complete solution for you. You need to split your total problem into its components and create or look for a solution to each component.

You have strings that contain CE dates in the format “dd/mm/yyyy”. These dates could be surrounded by text. You give the example “dd/mm/yyyy ttt”. Can ttt contain spaces? Could the “ttt” come before the date? Could the string be as complicated as “aaaa bbbb cccc 12/11/2016 dddd eeee ffff”?

Whatever the situation, I suspect something like:

Dim Part() As String

Part = Split(.Cells(R, C).Value," ")

would be the core of first step. With my complicated example, this would give:

Part(0) = "aaaa"
Part(1) = "bbbb"
Part(2) = "cccc"
Part(3) = "12/11/2016"
  :   :   :   :

A loop over the parts of each cell value looking for a string for which IsDate gives True would allow you to find the date so .Cells(R, C).Value = Part(N) would delete the unwanted text.

I would take a copy of your data and try to code a macro that discards the unwanted text. If you can successfully create that macro, you have completed step 1 of your solution. If you have trouble with this macro, you can ask for help here and expect to get it.

The next step is to convert the string “dd/mm/yyyy” to an Excel date. Excel holds dates as the number of dates since 1/1/1900 CE. Replacing: .Cells(R, C).Value = Part(N)by .Cells(R, C).Value = CDate(Part(N))ought to do the trick. However, Excel sometimes tries to interpret “dd/mm/yyyy” dates as “mm/dd/yyyy”. I think you will be alright but be aware of this possibility.

Your last step is to convert a date from the CE calendar to the Hijri calendar. This is not just a format issue. The two calendars have different year zeroes and different month lengths. There may be a standard conversion function in your country but there does not appear to be one here in the UK. There is help online so you should be able to find a function that will perform the conversion.

You have a number of separate problems and it is unrealistic to expect someone else to have posted a complete solution to that set of problems or that someone will code a complete solution for you. You need to split your total problem into its components and create or look for a solution to each component.

You have strings that contain CE dates in the format “dd/mm/yyyy”. These dates could be surrounded by text. You give the example “dd/mm/yyyy ttt”. Can ttt contain spaces? Could the “ttt” come before the date? Could the string be as complicated as “aaaa bbbb cccc 12/11/2016 dddd eeee ffff”?

Whatever the situation, I suspect something like:

Dim Part() As String

Part = Split(.Cells(R, C).Value," ")

would be the core of first step. With my complicated example, this would give:

Part(0) = "aaaa"
Part(1) = "bbbb"
Part(2) = "cccc"
Part(3) = "12/11/2016"
  :   :   :   :

A loop over the parts of each cell value looking for a string for which IsDate gives True would allow you to find the date so .Cells(R, C).Value = Part(N) would delete the unwanted text.

I would take a copy of your data and try to code a macro that discards the unwanted text. If you can successfully create that macro, you have completed step 1 of your solution. If you have trouble with this macro, you can ask for help here and expect to get it.

The next step is to convert the string “dd/mm/yyyy” to an Excel date. Excel holds dates as the number of dates since 1/1/1900 CE. Replacing: .Cells(R, C).Value = Part(N)by .Cells(R, C).Value = CDate(Part(N))ought to do the trick. However, Excel sometimes tries to interpret “dd/mm/yyyy” dates as “mm/dd/yyyy”. I think you will be alright but be aware of this possibility.

Your last step is to convert a date from the CE calendar to the Hijri calendar. This is not just a format issue. The two calendars have different year zeroes and different month lengths. There may be a standard conversion function in your country but there does not appear to be one here in the UK. There is help online so you should be able to find a function that will perform the conversion.

Tony Dallimore
  • 12,335
  • 7
  • 32
  • 61
  • Thank you Mr. @TonyDallimore for your answer .. I will try to write the code and update you with my results .. – Abeer Nov 12 '16 at 13:02