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
  • 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 '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

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
date_to_msidx 07/05/1998

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.