# How to Convert Vector number 5-digit number into date format

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?

• What is 43376? What time does it represent, what's the conversion? epochconverter.com – michael Nov 30 '18 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 '18 at 4:02
• 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 '18 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 '18 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 '18 at 4:50

## 1 Answer

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.

• 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 '18 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 '18 at 21:32