How to Convert Vector number 5-digit number into date format
Clash Royale CLAN TAG#URR8PPP
up vote
1
down vote
favorite
I am trying to convert the date format something like 10/03/2018 to 43376 in shell scripting, this can be done by using format cell option as the number in excel. How can do it in shell scripting?
date conversion
edited Nov 30 at 10:41
Jeff Schaller
37.4k1052121
asked Nov 30 at 3:52
user819529
82
|
show 1 more comment
up vote
1
down vote
favorite
I am trying to convert the date format something like 10/03/2018 to 43376 in shell scripting, this can be done by using format cell option as the number in excel. How can do it in shell scripting?
date conversion
edited Nov 30 at 10:41
Jeff Schaller
37.4k1052121
asked Nov 30 at 3:52
user819529
82
What is 43376? What time does it represent, what's the conversion? epochconverter.com
– michael
Nov 30 at 3:59
43376 is the value of the date column in SQL table. If we convert that number in excel by using format cell option as a date it will convert as 10/03/2018.
– user819529
Nov 30 at 4:02
2
But what is the number? It's not seconds since 1/1/1970, or days since (whenever). I can't guess what the number represents, so I can't give you a conversion function to use in a Linux shell (also: is this bash?)
– michael
Nov 30 at 4:20
@michael Number represents the date in a table, by that date all the columns values will be added by the one of the application but that application not using date format(like 10/03/2018),instead date format application inserting the values as 43376.(yes this is bash)
– user819529
Nov 30 at 4:27
43376*86400in unix epoch seconds is 10/03/2008 which is oddly exactly a decade off the example date value (days-since-epoch comes up in Samba and LDAP contexts). but that's just a guess
– thrig
Nov 30 at 4:50
|
show 1 more comment
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I am trying to convert the date format something like 10/03/2018 to 43376 in shell scripting, this can be done by using format cell option as the number in excel. How can do it in shell scripting?
date conversion
edited Nov 30 at 10:41
Jeff Schaller
37.4k1052121
asked Nov 30 at 3:52
user819529
82
I am trying to convert the date format something like 10/03/2018 to 43376 in shell scripting, this can be done by using format cell option as the number in excel. How can do it in shell scripting?
date conversion
date conversion
edited Nov 30 at 10:41
Jeff Schaller
37.4k1052121
asked Nov 30 at 3:52
user819529
82
edited Nov 30 at 10:41
Jeff Schaller
37.4k1052121
asked Nov 30 at 3:52
user819529
82
edited Nov 30 at 10:41
Jeff Schaller
37.4k1052121
edited Nov 30 at 10:41
Jeff Schaller
37.4k1052121
edited Nov 30 at 10:41
Jeff Schaller
37.4k1052121
37.4k1052121
asked Nov 30 at 3:52
user819529
82
asked Nov 30 at 3:52
user819529
82
asked Nov 30 at 3:52
user819529
82
82
What is 43376? What time does it represent, what's the conversion? epochconverter.com
– michael
Nov 30 at 3:59
43376 is the value of the date column in SQL table. If we convert that number in excel by using format cell option as a date it will convert as 10/03/2018.
– user819529
Nov 30 at 4:02
2
But what is the number? It's not seconds since 1/1/1970, or days since (whenever). I can't guess what the number represents, so I can't give you a conversion function to use in a Linux shell (also: is this bash?)
– michael
Nov 30 at 4:20
@michael Number represents the date in a table, by that date all the columns values will be added by the one of the application but that application not using date format(like 10/03/2018),instead date format application inserting the values as 43376.(yes this is bash)
– user819529
Nov 30 at 4:27
43376*86400in unix epoch seconds is 10/03/2008 which is oddly exactly a decade off the example date value (days-since-epoch comes up in Samba and LDAP contexts). but that's just a guess
– thrig
Nov 30 at 4:50
|
show 1 more comment
What is 43376? What time does it represent, what's the conversion? epochconverter.com
– michael
Nov 30 at 3:59
43376 is the value of the date column in SQL table. If we convert that number in excel by using format cell option as a date it will convert as 10/03/2018.
– user819529
Nov 30 at 4:02
2
But what is the number? It's not seconds since 1/1/1970, or days since (whenever). I can't guess what the number represents, so I can't give you a conversion function to use in a Linux shell (also: is this bash?)
– michael
Nov 30 at 4:20
@michael Number represents the date in a table, by that date all the columns values will be added by the one of the application but that application not using date format(like 10/03/2018),instead date format application inserting the values as 43376.(yes this is bash)
– user819529
Nov 30 at 4:27
43376*86400in unix epoch seconds is 10/03/2008 which is oddly exactly a decade off the example date value (days-since-epoch comes up in Samba and LDAP contexts). but that's just a guess
– thrig
Nov 30 at 4:50
What is 43376? What time does it represent, what's the conversion? epochconverter.com
– michael
Nov 30 at 3:59
What is 43376? What time does it represent, what's the conversion? epochconverter.com
– michael
Nov 30 at 3:59
43376 is the value of the date column in SQL table. If we convert that number in excel by using format cell option as a date it will convert as 10/03/2018.
– user819529
Nov 30 at 4:02
43376 is the value of the date column in SQL table. If we convert that number in excel by using format cell option as a date it will convert as 10/03/2018.
– user819529
Nov 30 at 4:02
2
2
But what is the number? It's not seconds since 1/1/1970, or days since (whenever). I can't guess what the number represents, so I can't give you a conversion function to use in a Linux shell (also: is this bash?)
– michael
Nov 30 at 4:20
But what is the number? It's not seconds since 1/1/1970, or days since (whenever). I can't guess what the number represents, so I can't give you a conversion function to use in a Linux shell (also: is this bash?)
– michael
Nov 30 at 4:20
@michael Number represents the date in a table, by that date all the columns values will be added by the one of the application but that application not using date format(like 10/03/2018),instead date format application inserting the values as 43376.(yes this is bash)
– user819529
Nov 30 at 4:27
@michael Number represents the date in a table, by that date all the columns values will be added by the one of the application but that application not using date format(like 10/03/2018),instead date format application inserting the values as 43376.(yes this is bash)
– user819529
Nov 30 at 4:27
43376*86400 in unix epoch seconds is 10/03/2008 which is oddly exactly a decade off the example date value (days-since-epoch comes up in Samba and LDAP contexts). but that's just a guess– thrig
Nov 30 at 4:50
43376*86400 in unix epoch seconds is 10/03/2008 which is oddly exactly a decade off the example date value (days-since-epoch comes up in Samba and LDAP contexts). but that's just a guess– thrig
Nov 30 at 4:50
|
show 1 more comment
1 Answer
1
active
oldest
votes
up vote
2
down vote
accepted
Assuming the info on this blog is true, this formula should do it:
date_to_msidx() echo $(( $(TZ=UTC date -d "$1" +%s) / 86400 + 25569));
date_to_msidx 10/03/2018
43376
date_to_msidx 07/05/1998
35981
This is "The 1900 Date System" of Microsoft; days since 1st January 1900 starting from 1, but incorrectly considering 1900 a leap year (one day longer than in reality).
The formula first use date +%s to convert it to Unix time (~secs since 1970/01/01), divides it by the number of seconds in a day (86400), then adds the number of days from 1900/01/01 to 1970/01/01 (25569) precalculated using the system above.
This is assuming GNU date; adapting it for other system/languages is left as an exercise to the reader.
answered Nov 30 at 4:52
pizdelect
15815
I am able to convert the date to the number but it not giving the correct date with that. If I change it as date_to_msidx() echo $(( $(date -d "$1" +%s) / 86400 + 25569)); It is giving the accurate dates. Even I calculated the days between 1900 to 1970 it is coming only 2567 days but how 2569/70 is correct I am not able to figure out.
– user819529
Nov 30 at 18:59
You're right, it was a stupid noob mistake (I forgot thatdateis also considering the timezone, it should beTZ=UTC date). As to why it's 25569 instead of 25567, it's +1 because it starts from 1, not from 0, and +1 because it's wrongly considering 1900 a leap year (with february 1900 having 29 instead of 28 days).
– pizdelect
Nov 30 at 21:32
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
Assuming the info on this blog is true, this formula should do it:
date_to_msidx() echo $(( $(TZ=UTC date -d "$1" +%s) / 86400 + 25569));
date_to_msidx 10/03/2018
43376
date_to_msidx 07/05/1998
35981
This is "The 1900 Date System" of Microsoft; days since 1st January 1900 starting from 1, but incorrectly considering 1900 a leap year (one day longer than in reality).
The formula first use date +%s to convert it to Unix time (~secs since 1970/01/01), divides it by the number of seconds in a day (86400), then adds the number of days from 1900/01/01 to 1970/01/01 (25569) precalculated using the system above.
This is assuming GNU date; adapting it for other system/languages is left as an exercise to the reader.
answered Nov 30 at 4:52
pizdelect
15815
I am able to convert the date to the number but it not giving the correct date with that. If I change it as date_to_msidx() echo $(( $(date -d "$1" +%s) / 86400 + 25569)); It is giving the accurate dates. Even I calculated the days between 1900 to 1970 it is coming only 2567 days but how 2569/70 is correct I am not able to figure out.
– user819529
Nov 30 at 18:59
You're right, it was a stupid noob mistake (I forgot thatdateis also considering the timezone, it should beTZ=UTC date). As to why it's 25569 instead of 25567, it's +1 because it starts from 1, not from 0, and +1 because it's wrongly considering 1900 a leap year (with february 1900 having 29 instead of 28 days).
– pizdelect
Nov 30 at 21:32
add a comment |
up vote
2
down vote
accepted
Assuming the info on this blog is true, this formula should do it:
date_to_msidx() echo $(( $(TZ=UTC date -d "$1" +%s) / 86400 + 25569));
date_to_msidx 10/03/2018
43376
date_to_msidx 07/05/1998
35981
This is "The 1900 Date System" of Microsoft; days since 1st January 1900 starting from 1, but incorrectly considering 1900 a leap year (one day longer than in reality).
The formula first use date +%s to convert it to Unix time (~secs since 1970/01/01), divides it by the number of seconds in a day (86400), then adds the number of days from 1900/01/01 to 1970/01/01 (25569) precalculated using the system above.
This is assuming GNU date; adapting it for other system/languages is left as an exercise to the reader.
answered Nov 30 at 4:52
pizdelect
15815
I am able to convert the date to the number but it not giving the correct date with that. If I change it as date_to_msidx() echo $(( $(date -d "$1" +%s) / 86400 + 25569)); It is giving the accurate dates. Even I calculated the days between 1900 to 1970 it is coming only 2567 days but how 2569/70 is correct I am not able to figure out.
– user819529
Nov 30 at 18:59
You're right, it was a stupid noob mistake (I forgot thatdateis also considering the timezone, it should beTZ=UTC date). As to why it's 25569 instead of 25567, it's +1 because it starts from 1, not from 0, and +1 because it's wrongly considering 1900 a leap year (with february 1900 having 29 instead of 28 days).
– pizdelect
Nov 30 at 21:32
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
Assuming the info on this blog is true, this formula should do it:
date_to_msidx() echo $(( $(TZ=UTC date -d "$1" +%s) / 86400 + 25569));
date_to_msidx 10/03/2018
43376
date_to_msidx 07/05/1998
35981
This is "The 1900 Date System" of Microsoft; days since 1st January 1900 starting from 1, but incorrectly considering 1900 a leap year (one day longer than in reality).
The formula first use date +%s to convert it to Unix time (~secs since 1970/01/01), divides it by the number of seconds in a day (86400), then adds the number of days from 1900/01/01 to 1970/01/01 (25569) precalculated using the system above.
This is assuming GNU date; adapting it for other system/languages is left as an exercise to the reader.
answered Nov 30 at 4:52
pizdelect
15815
Assuming the info on this blog is true, this formula should do it:
date_to_msidx() echo $(( $(TZ=UTC date -d "$1" +%s) / 86400 + 25569));
date_to_msidx 10/03/2018
43376
date_to_msidx 07/05/1998
35981
This is "The 1900 Date System" of Microsoft; days since 1st January 1900 starting from 1, but incorrectly considering 1900 a leap year (one day longer than in reality).
The formula first use date +%s to convert it to Unix time (~secs since 1970/01/01), divides it by the number of seconds in a day (86400), then adds the number of days from 1900/01/01 to 1970/01/01 (25569) precalculated using the system above.
This is assuming GNU date; adapting it for other system/languages is left as an exercise to the reader.
answered Nov 30 at 4:52
pizdelect
15815
edited Nov 30 at 21:35
answered Nov 30 at 4:52
pizdelect
15815
answered Nov 30 at 4:52
pizdelect
15815
answered Nov 30 at 4:52
pizdelect
15815
15815
I am able to convert the date to the number but it not giving the correct date with that. If I change it as date_to_msidx() echo $(( $(date -d "$1" +%s) / 86400 + 25569)); It is giving the accurate dates. Even I calculated the days between 1900 to 1970 it is coming only 2567 days but how 2569/70 is correct I am not able to figure out.
– user819529
Nov 30 at 18:59
You're right, it was a stupid noob mistake (I forgot thatdateis also considering the timezone, it should beTZ=UTC date). As to why it's 25569 instead of 25567, it's +1 because it starts from 1, not from 0, and +1 because it's wrongly considering 1900 a leap year (with february 1900 having 29 instead of 28 days).
– pizdelect
Nov 30 at 21:32
add a comment |
I am able to convert the date to the number but it not giving the correct date with that. If I change it as date_to_msidx() echo $(( $(date -d "$1" +%s) / 86400 + 25569)); It is giving the accurate dates. Even I calculated the days between 1900 to 1970 it is coming only 2567 days but how 2569/70 is correct I am not able to figure out.
– user819529
Nov 30 at 18:59
You're right, it was a stupid noob mistake (I forgot thatdateis also considering the timezone, it should beTZ=UTC date). As to why it's 25569 instead of 25567, it's +1 because it starts from 1, not from 0, and +1 because it's wrongly considering 1900 a leap year (with february 1900 having 29 instead of 28 days).
– pizdelect
Nov 30 at 21:32
I am able to convert the date to the number but it not giving the correct date with that. If I change it as date_to_msidx() echo $(( $(date -d "$1" +%s) / 86400 + 25569)); It is giving the accurate dates. Even I calculated the days between 1900 to 1970 it is coming only 2567 days but how 2569/70 is correct I am not able to figure out.
– user819529
Nov 30 at 18:59
I am able to convert the date to the number but it not giving the correct date with that. If I change it as date_to_msidx() echo $(( $(date -d "$1" +%s) / 86400 + 25569)); It is giving the accurate dates. Even I calculated the days between 1900 to 1970 it is coming only 2567 days but how 2569/70 is correct I am not able to figure out.
– user819529
Nov 30 at 18:59
You're right, it was a stupid noob mistake (I forgot that
date is also considering the timezone, it should be TZ=UTC date). As to why it's 25569 instead of 25567, it's +1 because it starts from 1, not from 0, and +1 because it's wrongly considering 1900 a leap year (with february 1900 having 29 instead of 28 days).– pizdelect
Nov 30 at 21:32
You're right, it was a stupid noob mistake (I forgot that
date is also considering the timezone, it should be TZ=UTC date). As to why it's 25569 instead of 25567, it's +1 because it starts from 1, not from 0, and +1 because it's wrongly considering 1900 a leap year (with february 1900 having 29 instead of 28 days).– pizdelect
Nov 30 at 21:32
add a comment |
Thanks for contributing an answer to Unix & Linux Stack Exchange!
- 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%2funix.stackexchange.com%2fquestions%2f485062%2fhow-to-convert-vector-number-5-digit-number-into-date-format%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
What is 43376? What time does it represent, what's the conversion? epochconverter.com
– michael
Nov 30 at 3:59
43376 is the value of the date column in SQL table. If we convert that number in excel by using format cell option as a date it will convert as 10/03/2018.
– user819529
Nov 30 at 4:02
2
But what is the number? It's not seconds since 1/1/1970, or days since (whenever). I can't guess what the number represents, so I can't give you a conversion function to use in a Linux shell (also: is this bash?)
– michael
Nov 30 at 4:20
@michael Number represents the date in a table, by that date all the columns values will be added by the one of the application but that application not using date format(like 10/03/2018),instead date format application inserting the values as 43376.(yes this is bash)
– user819529
Nov 30 at 4:27
43376*86400in unix epoch seconds is 10/03/2008 which is oddly exactly a decade off the example date value (days-since-epoch comes up in Samba and LDAP contexts). but that's just a guess– thrig
Nov 30 at 4:50