Hijri Dates to Gregorian Excel
up vote
1
down vote
favorite
I've got some data would take me hours to standardize if I go through it one by one.
01-06-1438 01-06-1439
1-6-1436 30-5-1437
01-6-1437 01-06-1438
Now I need something that can do this on the fly and push out something like this
01-06-1438 01-06-1439 28-02-2017 17-02-2018
Thanks and Best Regards
Edit: I'm using a user defined module to sort this one out, Its working nicely so far.
microsoft-excel microsoft-excel-2010
asked Dec 5 '17 at 11:23
Herubrand
1117
add a comment |
up vote
1
down vote
favorite
I've got some data would take me hours to standardize if I go through it one by one.
01-06-1438 01-06-1439
1-6-1436 30-5-1437
01-6-1437 01-06-1438
Now I need something that can do this on the fly and push out something like this
01-06-1438 01-06-1439 28-02-2017 17-02-2018
Thanks and Best Regards
Edit: I'm using a user defined module to sort this one out, Its working nicely so far.
microsoft-excel microsoft-excel-2010
asked Dec 5 '17 at 11:23
Herubrand
1117
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I've got some data would take me hours to standardize if I go through it one by one.
01-06-1438 01-06-1439
1-6-1436 30-5-1437
01-6-1437 01-06-1438
Now I need something that can do this on the fly and push out something like this
01-06-1438 01-06-1439 28-02-2017 17-02-2018
Thanks and Best Regards
Edit: I'm using a user defined module to sort this one out, Its working nicely so far.
microsoft-excel microsoft-excel-2010
asked Dec 5 '17 at 11:23
Herubrand
1117
I've got some data would take me hours to standardize if I go through it one by one.
01-06-1438 01-06-1439
1-6-1436 30-5-1437
01-6-1437 01-06-1438
Now I need something that can do this on the fly and push out something like this
01-06-1438 01-06-1439 28-02-2017 17-02-2018
Thanks and Best Regards
Edit: I'm using a user defined module to sort this one out, Its working nicely so far.
microsoft-excel microsoft-excel-2010
microsoft-excel microsoft-excel-2010
asked Dec 5 '17 at 11:23
Herubrand
1117
asked Dec 5 '17 at 11:23
Herubrand
1117
edited Dec 11 '17 at 11:48
asked Dec 5 '17 at 11:23
Herubrand
1117
asked Dec 5 '17 at 11:23
Herubrand
1117
asked Dec 5 '17 at 11:23
Herubrand
1117
1117
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
- On the Format menu, click Cells, and then click the Number tab.
- Select Custom from the list of categories.
- In the Type box, do one of the following:
Gregorian Format - "B1dd/mm/yy" - To display dates using the Gregorian calendar, regardless of the Regional Options or Regional Settings of the Microsoft Windows Control Panel setting, type B1 before the date format.
Hijri Format - "B2dd/mm/yy" – to type the date in Gregorian format but it will be interpreted and displayed as Hijri date.
answered Dec 5 '17 at 11:51
Mehper C. Palavuzlar
43.3k42174233
I want to convert the dates and this option doesn't seem to work.
– Herubrand
Dec 5 '17 at 12:31
1
@Herubrand Dates are just numbers, there is nothing to convert. 42794 is the number used to represent Gregorian date 28-02-2017 and also Hijri date 02-06-1438.
– Blackwood
Dec 5 '17 at 13:26
add a comment |
up vote
0
down vote
Say the value in A1 displays as:
01-06-1438
If this is a genuine Excel date, then just changing the format will convert it to Gregorian.
However, if changing the format does not change the cell display, then the data is Text.
It is very easy to convert the Hijri text-date into a Gregorian true date:
In K1 enter:
=TEXT(ROW(),"B2dd-mm-yyyy")
and in L1 enter:
=ROW()
Format L1 as dd-mm-yyyy. Then copy K1 and L1 down through row 1000000
Now we have a VLOOKUP table. In B1 enter:
=VLOOKUP(A1,K:L,2,FALSE)
and format B1 like L1:
answered Dec 5 '17 at 14:55
Gary's Student
13.3k31729
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1274419%2fhijri-dates-to-gregorian-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
- On the Format menu, click Cells, and then click the Number tab.
- Select Custom from the list of categories.
- In the Type box, do one of the following:
Gregorian Format - "B1dd/mm/yy" - To display dates using the Gregorian calendar, regardless of the Regional Options or Regional Settings of the Microsoft Windows Control Panel setting, type B1 before the date format.
Hijri Format - "B2dd/mm/yy" – to type the date in Gregorian format but it will be interpreted and displayed as Hijri date.
answered Dec 5 '17 at 11:51
Mehper C. Palavuzlar
43.3k42174233
I want to convert the dates and this option doesn't seem to work.
– Herubrand
Dec 5 '17 at 12:31
1
@Herubrand Dates are just numbers, there is nothing to convert. 42794 is the number used to represent Gregorian date 28-02-2017 and also Hijri date 02-06-1438.
– Blackwood
Dec 5 '17 at 13:26
add a comment |
up vote
0
down vote
- On the Format menu, click Cells, and then click the Number tab.
- Select Custom from the list of categories.
- In the Type box, do one of the following:
Gregorian Format - "B1dd/mm/yy" - To display dates using the Gregorian calendar, regardless of the Regional Options or Regional Settings of the Microsoft Windows Control Panel setting, type B1 before the date format.
Hijri Format - "B2dd/mm/yy" – to type the date in Gregorian format but it will be interpreted and displayed as Hijri date.
answered Dec 5 '17 at 11:51
Mehper C. Palavuzlar
43.3k42174233
I want to convert the dates and this option doesn't seem to work.
– Herubrand
Dec 5 '17 at 12:31
1
@Herubrand Dates are just numbers, there is nothing to convert. 42794 is the number used to represent Gregorian date 28-02-2017 and also Hijri date 02-06-1438.
– Blackwood
Dec 5 '17 at 13:26
add a comment |
up vote
0
down vote
up vote
0
down vote
- On the Format menu, click Cells, and then click the Number tab.
- Select Custom from the list of categories.
- In the Type box, do one of the following:
Gregorian Format - "B1dd/mm/yy" - To display dates using the Gregorian calendar, regardless of the Regional Options or Regional Settings of the Microsoft Windows Control Panel setting, type B1 before the date format.
Hijri Format - "B2dd/mm/yy" – to type the date in Gregorian format but it will be interpreted and displayed as Hijri date.
answered Dec 5 '17 at 11:51
Mehper C. Palavuzlar
43.3k42174233
- On the Format menu, click Cells, and then click the Number tab.
- Select Custom from the list of categories.
- In the Type box, do one of the following:
Gregorian Format - "B1dd/mm/yy" - To display dates using the Gregorian calendar, regardless of the Regional Options or Regional Settings of the Microsoft Windows Control Panel setting, type B1 before the date format.
Hijri Format - "B2dd/mm/yy" – to type the date in Gregorian format but it will be interpreted and displayed as Hijri date.
answered Dec 5 '17 at 11:51
Mehper C. Palavuzlar
43.3k42174233
answered Dec 5 '17 at 11:51
Mehper C. Palavuzlar
43.3k42174233
answered Dec 5 '17 at 11:51
Mehper C. Palavuzlar
43.3k42174233
answered Dec 5 '17 at 11:51
Mehper C. Palavuzlar
43.3k42174233
43.3k42174233
I want to convert the dates and this option doesn't seem to work.
– Herubrand
Dec 5 '17 at 12:31
1
@Herubrand Dates are just numbers, there is nothing to convert. 42794 is the number used to represent Gregorian date 28-02-2017 and also Hijri date 02-06-1438.
– Blackwood
Dec 5 '17 at 13:26
add a comment |
I want to convert the dates and this option doesn't seem to work.
– Herubrand
Dec 5 '17 at 12:31
1
@Herubrand Dates are just numbers, there is nothing to convert. 42794 is the number used to represent Gregorian date 28-02-2017 and also Hijri date 02-06-1438.
– Blackwood
Dec 5 '17 at 13:26
I want to convert the dates and this option doesn't seem to work.
– Herubrand
Dec 5 '17 at 12:31
I want to convert the dates and this option doesn't seem to work.
– Herubrand
Dec 5 '17 at 12:31
1
1
@Herubrand Dates are just numbers, there is nothing to convert. 42794 is the number used to represent Gregorian date 28-02-2017 and also Hijri date 02-06-1438.
– Blackwood
Dec 5 '17 at 13:26
@Herubrand Dates are just numbers, there is nothing to convert. 42794 is the number used to represent Gregorian date 28-02-2017 and also Hijri date 02-06-1438.
– Blackwood
Dec 5 '17 at 13:26
add a comment |
up vote
0
down vote
Say the value in A1 displays as:
01-06-1438
If this is a genuine Excel date, then just changing the format will convert it to Gregorian.
However, if changing the format does not change the cell display, then the data is Text.
It is very easy to convert the Hijri text-date into a Gregorian true date:
In K1 enter:
=TEXT(ROW(),"B2dd-mm-yyyy")
and in L1 enter:
=ROW()
Format L1 as dd-mm-yyyy. Then copy K1 and L1 down through row 1000000
Now we have a VLOOKUP table. In B1 enter:
=VLOOKUP(A1,K:L,2,FALSE)
and format B1 like L1:
answered Dec 5 '17 at 14:55
Gary's Student
13.3k31729
add a comment |
up vote
0
down vote
Say the value in A1 displays as:
01-06-1438
If this is a genuine Excel date, then just changing the format will convert it to Gregorian.
However, if changing the format does not change the cell display, then the data is Text.
It is very easy to convert the Hijri text-date into a Gregorian true date:
In K1 enter:
=TEXT(ROW(),"B2dd-mm-yyyy")
and in L1 enter:
=ROW()
Format L1 as dd-mm-yyyy. Then copy K1 and L1 down through row 1000000
Now we have a VLOOKUP table. In B1 enter:
=VLOOKUP(A1,K:L,2,FALSE)
and format B1 like L1:
answered Dec 5 '17 at 14:55
Gary's Student
13.3k31729
add a comment |
up vote
0
down vote
up vote
0
down vote
Say the value in A1 displays as:
01-06-1438
If this is a genuine Excel date, then just changing the format will convert it to Gregorian.
However, if changing the format does not change the cell display, then the data is Text.
It is very easy to convert the Hijri text-date into a Gregorian true date:
In K1 enter:
=TEXT(ROW(),"B2dd-mm-yyyy")
and in L1 enter:
=ROW()
Format L1 as dd-mm-yyyy. Then copy K1 and L1 down through row 1000000
Now we have a VLOOKUP table. In B1 enter:
=VLOOKUP(A1,K:L,2,FALSE)
and format B1 like L1:
answered Dec 5 '17 at 14:55
Gary's Student
13.3k31729
Say the value in A1 displays as:
01-06-1438
If this is a genuine Excel date, then just changing the format will convert it to Gregorian.
However, if changing the format does not change the cell display, then the data is Text.
It is very easy to convert the Hijri text-date into a Gregorian true date:
In K1 enter:
=TEXT(ROW(),"B2dd-mm-yyyy")
and in L1 enter:
=ROW()
Format L1 as dd-mm-yyyy. Then copy K1 and L1 down through row 1000000
Now we have a VLOOKUP table. In B1 enter:
=VLOOKUP(A1,K:L,2,FALSE)
and format B1 like L1:
answered Dec 5 '17 at 14:55
Gary's Student
13.3k31729
answered Dec 5 '17 at 14:55
Gary's Student
13.3k31729
answered Dec 5 '17 at 14:55
Gary's Student
13.3k31729
answered Dec 5 '17 at 14:55
Gary's Student
13.3k31729
13.3k31729
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1274419%2fhijri-dates-to-gregorian-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
