How to Convert Date to "Excel number" inside KM?

Questions & Suggestions
1

My KM Macro prompts the user for a Date in format e.g. May 2018

(Using a roughly "mid-month" date equivalent of 15 May 2018, this converts to the number 43235 in Excel when I convert it manually.)

How do I get the corresponding number in KM (not using an Excel formula) for other mid-month dates?

e.g. KM User enters June 2018 -------> add 30 to 43235 to match 15 June 2018 -----> 43265
e.g. KM User enters July 2018 -------> add (30+31) to 43235 to match 15 July 2018 -----> 43296

2

What is your end-objective? Do you really need the Excel number, or just a number that represents the date?

KM offers these Date and Time functions:

  • TIME function -- the time in unixtime, being seconds since the start of 1970 GMT.
  • MJD function -- the Modified Julian Date of the start of the year/month/day
  • ICU Date/Time tokens -- tokens (that work like functions) to get current date, date for specific month, day, year, and add/subtract from that.

I don't think KM has any Excel-related date/time functions. But if you know the Excel formula, you could construct it in KM.

Questions?

3

@cvc8445, just ran across this web page:
Convert Julian Date To Excel Calendar Date

Looks easy enough to convert to a KM formula.

4

@JMichaelTX asks a good question, specifically, what is you actual end goal, since knowing that can make it easier to find a solution instead of trying to do something in a specific way.

The Excel numbers are in datys since Jan 1, 1900. So you can get that number from Keyboard Maestro with:

MJD(Year,Month,Day)-MJD(1900,1,1)

So for example:

MJD(2018,5,15)-MJD(1900,1,1)

Although that comes up with 43233, not the 43235 you specify. You'll need to figure out why there is a discrepancy, although for your purposes of just the middle of the month, it may be irrelevant.

Converting month names to month numbers is left as an exercise.

1 Like
5

My end-objective is to take the KM-generated "number" e.g. 43235 and paste it into a database program that only understands that format for a "real date". Thanks.

6

@cvc8445,

I don't know what is causing this difference, but my testing in Excel Mac 2011 shows this:
Excel Serial Date = KM MJD(year,month,day) - 15018

image

Of course, you should do your own testing and confirm this formula.

IMO, this is easiest done using JavaScript (JXA here). This is a long-standing need, and scripts are readily available on the 'net, and maybe even here.

7

@cvc8445,

Just as I said. I quickly found this:

from which I adapted this simple JXA script:

 
'use strict';

var app = Application.currentApplication()
app.includeStandardAdditions = true

var kmInst = app.systemAttribute("KMINSTANCE");
var kmeApp = Application("Keyboard Maestro Engine");
var monthStr = kmeApp.getvariable('Local__MonthName', {instance: kmInst});

getMonthNum(monthStr);
 
 function getMonthNum(pMonthName){

   var d = Date.parse(pMonthName + " 1, 2017");
   if(!isNaN(d)){
      return new Date(d).getMonth() + 1;
   }
   return -1;
 }

Just put in an Execute JXA Action, and set a KM Variable Local__MonthName to, well, the month name (can be 3-char or full name).

1 Like