Why need to Double Click to every cell after convert Iranian date as text format to Persian date by Format Cell

[Mohammad Zilaei]

Hello to all

I have an Excel file that contains a number of Iranian dates in the text format as shown below in column A

1395/11/21

1395/12/25

1384/01/07

and ....





To perform mathematical calculations on this data, I converted column A to date format in the following way:

1- Range selection

2- Go to format cell

3- Select category: history

4- Select Locate: Persian (Iran)

5- Select Calendar: Persian

6- Select the Check Box : Input dates according to selected calendar

7- Then OK

But when I subtract the dates in column A, the error result #VALUE

But if I double click on both dates, the error is resolved

But I don't want to double click on all the cells one by one to fix the error.

I need a faster method

 

[levipe]

It sounds like the dates may be stored as text. Enter the number 1 into an unused cell. Copy it and then select your range and choose 'Paste special...'. In the dialog box that opens, choose values in the upper section and multiply in the lower section. That can force excel to convert numbers stored as text into actual numbers.

 

[Sufiyan97]

Hi & Welcome to MrExcel.

Another option

Select entire column with those dates
Go to > Data > Text to columns > Delimited > Next > Next > Date > YMD > Finish

 

[Mohammad Zilaei]

It sounds like the dates may be stored as text. Enter the number 1 into an unused cell. Copy it and then select your range and choose 'Paste special...'. In the dialog box that opens, choose values in the upper section and multiply in the lower section. That can force excel to convert numbers stored as text into actual numbers.

Thank you for taking the time to help me
I tested your solution and other similar solutions, but any mathematical operation on these dates (including multiplying by 1, adding to 0 and even joint "" to them) or using the VALUE function and the DATEVALUE function.
When you choose the option to use the Persian calendar, it considers the initial dates as a text before double-clicking, and after double-clicking, it converts them into a calculable date as a number. In the way that by applying the Persian calendar format on the cells, if you enter a date as 6/25/1402, it will accept it as a calculable Persian date.

 

[Sufiyan97]

Thank you for taking the time to help me
I tested your solution and other similar solutions, but any mathematical operation on these dates (including multiplying by 1, adding to 0 and even joint "" to them) or using the VALUE function and the DATEVALUE function.
When you choose the option to use the Persian calendar, it considers the initial dates as a text before double-clicking, and after double-clicking, it converts them into a calculable date as a number. In the way that by applying the Persian calendar format on the cells, if you enter a date as 6/25/1402, it will accept it as a calculable Persian date.

Have you tried my suggetion in post #3?

 

[Mohammad Zilaei]

Have you tried my suggetion in post #3?

Dear Sufiyan,
I am so sorry for my delay to reply.
Yes, I tested, but it was not useful.